Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 362
• Last Modified:

# 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
0
Lawrence Salvucci
Asked:
• 3
• 3
1 Solution

Commented:
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
You have all terms in t1, t2, t3, t4, t5

-FA
0

Information Technology ManagerAuthor Commented:
How do I call that function for each parsing?
0

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

-FA
0

Commented:
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

Information Technology ManagerAuthor Commented:
No I would want to use this in multiple cells.
0

Information Technology ManagerAuthor Commented:
I took a look at your example. I would need each of these parses put into separate cells.
0

Commented:
(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
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

## Featured Post

• 3
• 3
Tackle projects and never again get stuck behind a technical roadblock.