none
VBA SQL Split string RRS feed

  • Question

  • Hi, I have an excel file that i would like to retrieve a certain columm but i wanted to split the string like the sample below.

    my VBA SQL Query where the SKU would be the string to be split. Thank you.

        sqlstr = "SELECT e.SKU "
        sqlstr = sqlstr & "FROM [1.reference$] e "
        sqlstr = sqlstr & "WHERE isnull(e.Location) "

    -----Source string to be split------------Result 1 ----------------Result 2

    ----------------------------------------------------------------------------------
    TALK3000430iPH5S32SIL----300-0430-iPH5S32SIL------
    iPH5S32SIL
    MP3000440iPH5C16BLU------300-0440-iPH5C16BLU-----
    iPH5C16BLU
    TALK3003110REVERE3-------300-3110-REVERE3--------
    REVERE3
    TALK3003110LUCID3--------300-3110-LUCID3-----------
    LUCID3
    MP3000460iPH5S32GLD------300-0460-iPH5S32GLD----
    iPH5S32GLD
    TALK3004410ONERMX--------300-4410-ONERMX--------
    ONERMX

    Wednesday, September 24, 2014 6:19 AM

Answers

  • If you want to use VBA code this will split and format the way you want (if I understood it correctly):

    Dim SKU As String SKU = "TALK3000430iPH5S32SIL" Dim FirstPartIndex As Integer Dim ThirdPartIndex As Integer

    Dim Result0 As String Dim Result1 As String Dim Result2 As String FirstPartIndex = -1 ThirdPartIndex = -1 For i = 1 To Len(SKU) If Mid(SKU, i, 1) >= "0" And Mid(SKU, i, 1) <= "9" And FirstPartIndex = -1 Then FirstPartIndex = i End If If FirstPartIndex > 0 And ThirdPartIndex = -1 And Not (Mid(SKU, i, 1) >= "0" And Mid(SKU, i, 1) <= "9") Then ThirdPartIndex = i End If Next If FirstPartIndex > -1 And ThirdPartIndex > -1 Then Result0 = Left(SKU, FirstPartIndex - 1) Result1 = Format(Mid(SKU, FirstPartIndex, ThirdPartIndex - FirstPartIndex), "###-####") Result2 = Right(SKU, Len(SKU) - ThirdPartIndex + 1) Result1 = Result1 & "-" & Result2 End If



    "If there's nothing wrong with me, maybe there's something wrong with the universe!"


    Wednesday, September 24, 2014 8:29 AM