Parse Fields in Excel

I have a long values in column A that I need to parse into multiple columns

1. I need to parse everything from the beginning of the cell to where the word "Crystal" starts.
2. I need to parse everything from where the word "Crystal" begins to where the word "Report" ends.
3. I need to parse everything from where the word "Folders" begins to where the first letter of the month starts.
4. I need to parse everything from where the month begins to where either "AM" or "PM" end.
5. I need to parse everything (if there is anything) after either "PM" or "AM".

Example in cell A25:

Backlog Grind Hours - MAKE Crystal Report Folders/Special Analysis Nov 18, 2011 12:24 PM This is a TEST


Parses:

Backlog Grind Hours - MAKE
Crystal Report
Folders/Special Analysis
Nov 18, 2011 12:24 PM
This is a TEST
LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
Farzad AkbarnejadDeveloperCommented:
Try the sample. Note that cell B2 that holds result has Wrapped Text format.
Press Alt+F11 and see code in Module1.

-FA
sample.xlsm
0
 
Farzad AkbarnejadDeveloperCommented:
Hello,
I do it using VBA
Sub proc()
    v = Sheets(1).Cells(1, 25).Value
    x = InStr(1, v, "Crystal")
    t1 = Trim(Mid(v, 1, x - 1))
    y = InStr(x, v, "Report")
    t2 = Trim(Mid(v, x, y - x + 6))
    a1 = InStr(1, v, "Folders")
    a2 = InStr(a1, v, "Jan") + InStr(a1, v, "Feb") + InStr(a1, v, "Mar") + _
            InStr(a1, v, "Apr") + InStr(a1, v, "May") + InStr(a1, v, "Jun") + _
            InStr(a1, v, "Jul") + InStr(a1, v, "Aug") + InStr(a1, v, "Sep") + _
            InStr(a1, v, "Oct") + InStr(a1, v, "Nov") + InStr(a1, v, "Dec")
    t3 = Trim(Mid(v, a1, a2 - a1 - 1))
    a3 = InStr(a2, v, " PM") + InStr(a2, v, " AM") + 3
    t4 = Trim(Mid(v, a2, a3 - a2))
    t5 = Trim(Mid(v, a3))
End Sub

Open in new window

You have all terms in t1, t2, t3, t4, t5

-FA
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
How do I call that function for each parsing?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Farzad AkbarnejadDeveloperCommented:
Do you want to get the result in one cell?

-FA
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
No I would want to use this in multiple cells.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
I took a look at your example. I would need each of these parses put into separate cells.
0
 
Elton PascuaCommented:
(Points to FarzadA)

1. Modify FarzadA's code to show below (change the vbcrlf to | enclosed in double quotes)
ParseCell = t1 & "|" & t2 & "|" & t3 & "|" & t4 & "|" & t5

Open in new window

2. Drag his User Defined Formula down the rows.
3. Copy and paste the column as values.
4. Data > Text to Columns > Delimited. Uncheck everything else but "Other" then put | in the other field. Click Finish.

They should now be separated to columns.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.