How to parse an inconsistent text string to multiple columns

Hi.

I have had a look at your data. and it seems pretty Straight Forward.

Your Data Starts with Times like or 9a: 9:40a: 8:45a: 2p: So all you really need to do is replace each time ending with a: and p: with |a:| and |p:|

Then use the Text to Columns function to split your data at each "|"

The major hurdle we have is when the time and the preceding postal codes run into each other.

So the Question is do all your postal codes have 6 alpha numerics?

Paste this code into A Macro Module and run Macro1

Public Temp As String
Sub Macro1()
LR = Cells(Rows.Count, 1).End(xlUp).Row
    
With Range("B2:B" & LR)
.FormulaR1C1 = "=test(RC[-1])"
.Value = .Value
End With

Range("A:A").Delete

    Range("A2:A" & LR).TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, _
        1), Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1), Array(12 _
        , 1), Array(13, 1), Array(14, 1), Array(15, 1)), TrailingMinusNumbers:=True
        
End Sub


Function Test(A As String)
Temp = A
Temp = Pass("a:")
Test = Pass("p:")
End Function

Function Pass(T As String)
Temp = Replace(Temp, T, T & "|")
TextA = Split(Temp, T & "|")

For Count = 0 To UBound(TextA)

TT = TextA(Count)

'********************************************************************************************
' Look Wholly Numeric Entry.
Flag = True
For Count1 = 1 To Len(TT)
If Not IsNumeric(Mid(TT, Count1, 1)) And Mid(TT, Count1, 1) <> ":" Then Flag = False
Next

If Flag = True Then TextA(Count) = TextA(Count) & T: GoTo Skip


'********************************************************************************************
' Look for Time at End.
APos = Len(TT)

TimeLoop:
If IsNumeric(Mid(TT, APos, 1)) Or Mid(TT, APos, 1) = ":" Then APos = APos - 1: GoTo TimeLoop
If Not IsNumeric(Mid(TT, APos - 1, 1)) Then

'Date At End
Ltext = Left(TT, APos)
Rtext = Right(TT, Len(TT) - APos)

TextA(Count) = Ltext & "|" & Rtext & T

GoTo Skip
End If

'********************************************************************************************
' Look for Postcode With Space at End.
Check = Replace(Right(TT, 7), " ", "")

Flag = True
For CheckLoop = 1 To 5 Step 2
If IsNumeric(Mid(Check, CheckLoop, 1)) Or Not IsNumeric(Mid(Check, CheckLoop + 1, 1)) Then Flag = False
Next

If Flag = True Then
'Postcode at End Of Entry
GoTo Skip

End If


'********************************************************************************************
' Look for Postcode Without Space at End.
Check = Right(TT, 6)

Flag = True
For CheckLoop = 1 To 5 Step 2
If IsNumeric(Mid(Check, CheckLoop, 1)) Or Not IsNumeric(Mid(Check, CheckLoop + 1, 1)) Then Flag = False
Next

If Flag = True Then
'Postcode at End Of Entry

TextA(Count) = Left(TT, Len(TT) - 3) & " " & Right(TT, 3)
GoTo Skip

End If

'********************************************************************************************
'Look for Postcode with space & Time
SpaceCount = Len(TT) - Len(Replace(TT, " ", ""))

Temp = Application.Substitute(TT, " ", "|", SpaceCount - 1)

Check = Replace(Right(Temp, Len(Temp) - InStr(Temp, "|")), " ", "")

Flag = True
For CheckLoop = 1 To 5 Step 2
If IsNumeric(Mid(Check, CheckLoop, 1)) Or Not IsNumeric(Mid(Check, CheckLoop + 1, 1)) Then Flag = False
Next

If Flag = True Then
'Postcode at End Of Entry
TT = Application.Substitute(TT, " ", "|", SpaceCount - 1)

Ltext = Left(TT, InStr(TT, "|") - 1) & " " & Mid(TT, InStr(TT, "|") + 1, 7) & "|"
Rtext = Right(TT, Len(TT) - Len(Ltext) + 1)

TextA(Count) = Ltext & Rtext & T
GoTo Skip

End If

Skip:
Next

Pass = Join(TextA, "|")
End Function

Hi Everyone, Update on this post. Although your suggestions were great and one was too over my head to attempt I didn't really find my complete answer. In saying that I did take your advice and recorded a macro that would replace all a: and p: with | using the text to delimeter. I think my next step will be to use the script to find the postal codes in each column and delete anything after it. It isn't the perfect answer but it is better than nothing. Thanks again!

Originally Posted by aabr

Hi,

I have a system that will download a CSV file with information.

Problem: There is a column that will reads multiple times and address onto one text string in one cell. Sometimes there may be 1 address and 1 time and sometimes there may be 10 address with 10 times.

What I'd like the formula to do: I'd like to have a formula automatically look at all the information and separate the data. I've slowly been chipping away at it but I am very slow.

I was wondering if someone could guide me to a faster solution. I've attached a workbook with the first sheet showing how the data is outputted to how I want the data to show. The second sheet is pretty much useless but it shows you how little I've come so far.

To anyone who can help, I'd greatly appreciate it.

Thank you,

I've managed to locate all "delimiters(i.e. a: p: )" with merely a single formula (String to be parsed in A1)

Formula:

=IFERROR(ROW(INDIRECT("2:"&LEN(A1)))/ISNUMBER(MATCH(MID(A1,ROW(INDIRECT("1:"&LEN(A1)-1)),2),{"a","p"}&":",)),"")

ouput array is like this,

Formula:

{"";"";"";"";6;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";44;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";98;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";142;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";197;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";228;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";272;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";301;"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";"";""}

Pls refert to the cells in BLUE in the attached file

Unfortunately, due to inconsistency of time delimiters, say "8:45a:", "9a:", "3:30p:", it's way too difficult to further process the string by a single formula with aforementioned delimiter array; at least, nearly impossible for me
Nevertherless, hope my formula could be of help for u!

Cheers!

I've looked at your data. There's a possible complication to automating this. If a postal code ends with a 1 and the following time were 1a:, 1p:, 2a:, 2p: or any of those with :mm between hour and a or p, there's no way short of explicitly completing postal codes before parsing times to handle, for example, ..., ON, X3Y 2Z11:30p: correctly.

Parsing full addresses will be rather difficult since not all addresses include province and postal code or state and zip code. Assuming the only instances in which a 1 could appear immediately before a time would be as the final character in a postal code, you could use regular expressions to handle this. That requires VBA.

The approach I'd take is implementing a user-defined function with a reference to an external library, Micrisoft VBScript Regular Expressions 5.5.

Function regexextract(s As String, rep As String, Optional k As Long = 1) As Variant
  Dim re As New RegExp, mc As MatchCollection, m As Match

  re.Global = True
  re.IgnoreCase = True
  re.Pattern = rep

  Set mc = re.Execute(s)

  If 0 < k And k <= mc.Count Then
    regexextract = mc.Item(k - 1)
  Else
    regexextract = CVErr(xlErrNA)
  End If
End Function

Put this in a general VBA module in your workbook, and add a reference (in the VBA Editor, Tools - References) to the external library given above.

Use this UDF in formulas like

A10:
=regexextract($A2,"([1-9]|1[0-2])(:\d\d)?[ap]: ",1)

B10:
=regexextract(REPLACE($A2,1,SUMPRODUCT(LEN($A10:A10)),""),
"[^:]+(([A-Za-z ,.]+[A-Z]\d[A-Z] ?\d[A-Z]\d)|([A-Za-z ,.]+\d{5}(-\d{4})?))?(?=$|([1-9]|1[0-2])(:\d\d)?[ap]: )")

C10:
=regexextract(REPLACE($A2,1,SUMPRODUCT(LEN($A10:B10)),""),"([1-9]|1[0-2])(:\d\d)?[ap]: ")

Select B10:C10, copy. Move to D10, paste. Move to F10, paste. And so on to the right on row 10.

The A10 and C10 patterns locate hour (1 to 9 or 10 to 12) possibly followed by :mm then a or p followed by : and a space.

The B10 pattern is more complicated. It looks for a string of characters other than : possibly followed by a Canadian province and postal code (string of letters, spaces, commas or periods followed by letter-digit-letter possibly a space digit-letter-digit) or a US state and a zip code (string of letters, spaces, comma or periods followed by 5 digits then possibly by a hyphen and 4 more digits). Then it looks for BUT DOES NOT MATCH the next time or end of string.

Even using regular expressions, I can think of a few pathological incomplete addresses which could screw this up. That said, in my opinion, this sort of text parsing exercise is pointless without regular expressions.