CODEWITHSUNDEEP
regexvbacsvexcel
Zachary R
Zachary R

Reputation: 47

Separating a string in Excel VBA

I have a series (thousands and thousands) of call record that I'm trying to create a spreadsheet of. They're all in a text file. The format looks like this:

12/ 13/ 05 Syracuse, NY 10: 22 AM 111- 111- 1111 2 $ - $ - $ -
12/ 13/ 05 New York, NY 10: 28 AM 111- 111- 1111 (F) 2 $ - $ - $ -
12/ 13/ 05 Orlando, FL 10: 48 AM 111- 111- 1111 (F) 4 $ - $ - $ -

3/ 9/ 09 Internal 4: 51 PM 111- 111- 1111 (E) 23 $ - $ - $ -
10/ 14/ 11 Colorado Site 8: 12 AM 111- 111- 1111 14 $ - $ - $ -
1/ 3/ 12 Dept 27 3: 16 PM 111- 111- 1111 (F) 93 $ - $ - $ -

11/ 12/ 12 Internal 3: 13 PM 18765 (E) 16 $ - $ - $ -
11/ 14/ 12 Internal 11: 43 AM John Doe 3 $ - $ - $ -

Month/ day/ year/ city called, STATE HH: MM APM 123- 456 7890 OptionalCode $Charge $Tax $Total

This is, minus details, directly from the file. No quotes around strings, no tabs. I tried to use text to columns, but some cities do have space and others don't.

Anyone want to point me in the right direction? RegEx maybe (Which I've heard of but never used)? Something else?

Update: Thanks for the early feedback. The line are actual data from my file, though I stripped city and phone numbers. I've updated with the city information to show variance there. To the best I can see, none of the city names have a comma, but I'm dealing with close to 120,000 lines total and, obviously, haven't checked them all.

The city won't always, obviously, have a space - Syracuse above doesnt, New York, however, does. The month and date, too, aren't always 2 digits - which also throws off checks for length. I can read to first, then second forward slash, though - those are fixed after date and month values.

And the bracketed code doesn't always appear... sometimes it's there, sometimes not, though they do appear to only ever be one letter when they arrive.

I hope this clears a few things up. This would have been far easier if it was stored correctly in the first place. Sigh.

Updates 2,3 & 4 Added a few lines from call log changes per Robin's request.

Upvotes: 0

Views: 380

Answers (2)

robinCTS
robinCTS

Reputation: 5886

I know you asked for a VBA solution, but I do my call record parsing purely in a spreadsheet with formulae.

I have uploaded a workbook solution here (version 3).

Once you have the workbook open, copy and paste the contents of your text file into cell A2. Then fill down the range B2:X2 as far as necessary.

The formulae will work with any variation in length of month, day , year, city, state, time, code, charge, tax and total.

Let me know if any lines break. You can easily check for these by using the AutoFilter dropdown in the headers to select for errors/extraneous values. Append any offending lines to your question.

Updates:

Version 2 takes care of the situation where the City field contains a location name, and the State field is blank.

Version 3 takes care of the situation where the Phone Number field contains an extension number or name.

Upvotes: 3

safetyOtter
safetyOtter

Reputation: 1460

Something like this might work if there are no comma's in the city name.

Sub foo()
    thisLine = "12/ 13/ 05 City Name, ST 10: 28 AM 111- 111- 1111 (F) 2 $ - $ - $ -"
    thisDate = Mid(thisLine, 1, 10)
    thisLine = Mid(thisLine, 12)
    firstComma = InStr(1, thisLine, ",")
    City = Mid(thisLine, 1, firstComma - 1)
    thisLine = Mid(thisLine, firstComma + 2)
    State = Left(thisLine, 2)
    thisLine = Mid(thisLine, 4)
    thisTime = Left(thisLine, 9)
    thisLine = Mid(thisLine, 11)
    thisPhone = Left(thisLine, 14)
    thisLine = Mid(thisLine, 16)
    tempArray = Split(thisLine, "$")
    If UBound(tempArray) = 3 Then
    optionalCode = tempArray(0)
    charge = "$" & tempArray(1)
    tax = "$" & tempArray(2)
    Total = "$" & tempArray(3)
    Else
    ' throw an error something went wrong
    End If
End Sub

Upvotes: 1

Related Questions