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:
Lawrence Salvucci
  • 3
  • 3
1 Solution
 
Farzad AkbarnejadCommented:
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
 
Farzad AkbarnejadCommented:
Do you want to get the result in one cell?

-FA
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Farzad AkbarnejadCommented:
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
 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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