Txt to Columns w/ a "space", "5 random numbers", then a "space" or "dash"

[micahlange33]

This one is rather tricky I am assuming...

I have data that I need separated by an array of text and can be done in VBA if needed (note that it will be done for 1000's of lines and cycle time is a concern). I need to split the following data:

01609 0805ZG106ZAT 03041 C2012Y5V1A106Z ZZT09 LMK212F106ZG 08390 C0805C106Z8VAC 11962-CC0805ZKY5V6BB106

The underlined portions are what needs separated and you can see that it includes a space, five random numbers, and another space or a dash.

I cannot simply do a fixed width text to columns as the length of the data is variable between delimiters.
I cannot simply do a space delimiter as there is some data that will contain spaces within the data.
I cannot simply do a dash delimiter as there is some data that will contain dashes within the data.

This is why I have specified that I will need a space, five random numbers, and then a space or a dash....note that the first delimiter does not have a space before it and that it will be removed with a fixed length delimiter before removing and separating all subsequent data strings.

 

[Mindpsyche]

If the text is in Cell A1 in Cell A2 enter this formula = Substitute(A1,"-"," ")

This should give you a blank space instead of a hyphen which should help with the text to columns?

 

[adam087]

You say that you're looking for fove random 'numbers' but your example data underlines a set of five containing letters (ZZT09) - so are we looking for any set of five characters, or only five integers?

Thanks
Adam

 

[micahlange33]

Mindpsyshe - Replacing a dash with a space or vice versa will not work as the delimiter. See quoted text below from initial post:

"I cannot simply do a fixed width text to columns as the length of the data is variable between delimiters.
I cannot simply do a space delimiter as there is some data that will contain spaces within the data.
I cannot simply do a dash delimiter as there is some data that will contain dashes within the data."

adam087 - You are correct. I mistakenly put that it will only be numbers...it could either be numbers or letters.

Any insight on this would be very appreciated. This one has myself and a few others stumped...

 

[adam087]

Well; here's one option. Run the 'PrepareList' macro on your selection and then do a Text-to-Columns using "|" as your delimiter. Note that if your data contains the character | then it obviously won't work but it probably won't! You're right to be concerned about cycle times. I've just run it on 100,000 random rows (of the same length you provided) and it took about 40 secs on my reasonably good PC. Still, might do the job for you.

Option Explicit


Public Sub PrepareList()
    
    Dim r As Range
    
    For Each r In Selection
        r.Value = AddDelim(r.Value)
    Next r

End Sub



Private Function AddDelim(orig As String) As String

    Dim i As Integer, s As String, cnt As Integer, t As String
        s = ""
        cnt = 0
    
    For i = 1 To Len(orig)
    
        If cnt = 5 Then
            t = Right(s, 5)
            s = Left(s, Len(s) - 5)
            s = s & "|" & t
            cnt = 0
        End If
        
        If Asc(Mid(orig, i, 1)) > 47 And Asc(Mid(orig, i, 1)) < 58 Then
            cnt = cnt + 1
        Else
            cnt = 0
        End If
        
        s = s & Mid(orig, i, 1)
    
    Next i
    
    AddDelim = s

End Function

Regards
Adam

 

[Rick Rothstein]

This one is rather tricky I am assuming...

I have data that I need separated by an array of text and can be done in VBA if needed (note that it will be done for 1000's of lines and cycle time is a concern). I need to split the following data:

01609 0805ZG106ZAT 03041 C2012Y5V1A106Z ZZT09 LMK212F106ZG 08390 C0805C106Z8VAC 11962-CC0805ZKY5V6BB106

The underlined portions are what needs separated and you can see that it includes a space, five random numbers, and another space or a dash.

I cannot simply do a fixed width text to columns as the length of the data is variable between delimiters.
I cannot simply do a space delimiter as there is some data that will contain spaces within the data.
I cannot simply do a dash delimiter as there is some data that will contain dashes within the data.

This is why I have specified that I will need a space, five random numbers, and then a space or a dash....note that the first delimiter does not have a space before it and that it will be removed with a fixed length delimiter before removing and separating all subsequent data strings.

I am a little unclear on some points about what you want to do...

1) For the example line you gave, assuming it is in cell A2, are you looking to put 01609 into cell B2, 0805ZG106ZAT into C2, 03041 into D2, etc.?

2) What is to become of the space or dash separators... throw them away or keep them attached to the 5-character text (or maybe the data instead)?

3) You say it is possible for the data to contain spaces or dashes... I presume by data you mean the non-5-character text, right? Is it possible for such a space or dash to be located 5 characters in from either the left or right side of the data containing it and, if so, how are we to tell when its part of data and not a delimiter for the 5-character text?

4) Can the dash, as a delimiter, be located on either or both sides of the 5-character text?

5) Can the text in the 5-character text be lower case?

 

[micahlange33]

Thanks all!

I took a majority of the setup from Adam's post...had to do some slight tweaks but it provided solid logic and a basis for what was needed for my application.

Much apprecited