excel vba_specify special character then split to different row

Question

I have a sheet like below

DATA_DT G_GLACNO_OLD G_GLACCTNO

4/30/2017 1150007000 130134001L01 / L02 / L03 / L50

4/30/2017 1151009000 130150901L01 / L02 / L03 / L50

4/30/2017 1151014026 130154602L01 / L02 / L03 / L50

4/30/2017 1151014027 130154602L01 / L02 / L03 / L50

4/30/2017 1151015003 130154701L01 / L02 / L03 / L50

4/30/2017 1151015004 130154701L01 / L02 / L03 / L50

4/30/2017 1151015006 130154701L01 / L02 / L03 / L50

4/30/2017 1178036000 130974003L01 / L02 / L03 / L30 / L50

4/30/2017 1151015011 130154701L01 / L02 / L03 / L50

4/30/2017 1151015001 130154702L01 / L02 / L03 / L50

4/30/2017 1151015002 130154702L01 / L02 / L03 / L50

I would like split the row by the special character "/"

so

4/30/2017 1150007000 130134001L01 / L02 / L03 / L50

i want to become

4/30/2017 1150007000 130134001L02

4/30/2017 1150007000 130134001L03

4/30/2017 1150007000 130134001L50

4/30/2017 1150007000 130134001L01

Is there any quick solution?

4/30/2017 1150007000 130134001L01 / L02 / L03 / L50

There are some rows have 4 slashes, some rows have 5 slashes.

Welcome to StackOverflow. Please note, that this is not a free code-writing service. Yet, we are eager to help fellow programmers (and aspirants) writing their own code. Please read the help topics on [How do I Ask a Good Question](http://stackoverflow.com/help/how-to-ask). You might also want to [take the tour](https://stackoverflow.com/tour) and earn a badge while doing so. Afterwards, please update your question with the VBA code you have written thus far in order to complete the task(s) you wish to achieve. We will be here waiting for you. Ready to assist and help you finalize *your code*. — Ralph, Dec 04 '17 at 08:35

score 0 · Answer 1 · answered Dec 04 '17 at 09:24

What is said in the comments is quite true - you should have given it a try as far as this is not a code-writing service and etc.

However, as far as __________________________ (put any random thing here), I have decided to give it a try on your input-output:

Option Explicit

Public Sub TestMe()

    Debug.Print RemoveSomeSlashed("4/30/2017 1151015004 130154701L01 / L02 / L03 / L50")
    Debug.Print RemoveSomeSlashed("4/30/2017 11780 130974003L01 / L02 / L03 / L30 / L50")

End Sub

Public Function RemoveSomeSlashed(myInput As String) As String

    Dim cnt         As Long
    Dim startCnt    As Long
    Dim endCnt      As Long
    Dim compare     As String

    compare = Replace(myInput, "/", "")
    endCnt = Len(myInput) - Len(compare) - 2

    For cnt = Len(myInput) To 1 Step -1
        If Mid(myInput, cnt, 1) = "/" Then
            Mid(myInput, cnt, 1) = "?"
            startCnt = startCnt + 1
            If startCnt = endCnt Then
                RemoveSomeSlashed = Replace(myInput, "?", "")
                Exit Function
            End If
        End If
    Next cnt

End Function

That is not the best solution, but it works. The idea is that it removes all the slashes but two and you are not allowed to have the ? sign in the string.

As a bonus, it will display empty string if your text does not have at least 3 slashes :).

thanks Vityata, I found this one is helpful for me: https://stackoverflow.com/questions/42425208/vba-split-cell-values-into-multiple-rows-and-keep-other-data I will try the idea of this one. — Coty Huang, Dec 05 '17 at 03:32

excel vba_specify special character then split to different row

1 Answers1