Data Parsing Formulas

Topic

New Reply

Hi guys,
I am getting a data export that has different data values that I would like to parse into separate cells.

Spain, (EUROPE), Madrid- Human Capital

Sometimes some of the data export records have a leading space that TRIM doesn’t seem to get rid of.

I want to parse the data values into there own cells
Spain
Europe
Madrid
Human Capital

The attached workbook has the table layout.

Any help is always appreciated.

Thanks
Amy

Reply | Quote

Replies

In B2:
=TRIM(LEFT(A2,FIND(“, (“,A2)-1))

In C2:
=TRIM(MID(A2,FIND(“, (“,A2)+3,FIND(“), “,A2)-FIND(“, (“,A2)-3))

In D2:
=TRIM(MID(A2,FIND(“), “,A2)+3,FIND(“- “,A2)-FIND(“), “,A2)-3))

In E2:
=TRIM(MID(A2,FIND(“- “,A2)+2,LEN(A2)))

As to why TRIM may not work, the example does not seem to have any, but this can occur when what looks like a space is not really a space. Some programs just what WORD refers to as a “sticky space” which is an ASCII 160 which would not be trimmed since a space is ASCII 32.

To replace the ASCII 160 you could use something like:
=Substitute(A2, char(160), “”)

or even use find and replace (to enter the 160, hold while entering 0160 on the numeric keypad

Steve

Reply | Quote

Thanks, Steve.

I’ll see if the “sticky space” theory is true for the data exports this week. The example that I provided was just a mock-up of the data.

Amy

Reply | Quote

Hi Amy,

Here’s a macro to parse all the strings in a selected range:

Code:

Sub ParseStrings()
Application.ScreenUpdating = False
Dim oCel As Range, StrArr As String, i As Integer
For Each oCel In Selection
  StrArr = WorksheetFunction.Proper(Replace(Replace(Replace(Replace(oCel.Value, "(", ""), ")", ""), "-", ","), Chr(160), " "))
  For i = 0 To UBound(Split(StrArr, ","))
    oCel.Offset(0, i + 1).Value = Trim(Split(StrArr, ",")(i))
  Next
Next
Application.ScreenUpdating = True
End Sub

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote

Thanks, guys. I have noticed some discrepancies in the data, however, that prevent the exclusive use of the FIND(“, (“,A2) formula to do the parsing. Some records don’t have a Continent value. How can the formula arrays be expanded to accommodate this discrepancy?

I have uploaded a new example.

Thanks
Amy

Reply | Quote

Hi Amy,

The macro will parse correctly parse data such as “Libya, , Tripoli- Marketing” (per your example with missing continent data) – that field will simply be left blank.

However, you might also get better results if you change the line:
StrArr = WorksheetFunction.Proper(Replace(Replace(Replace(Replace(oCel.Value, “(“, “”), “)”, “”), “-“, “,”), Chr(160), ” “))
to:
StrArr = WorksheetFunction.Proper(Replace(Replace(Replace(Replace(oCel.Value, Chr(160), ” “), “(“, “”), “)”, “”), “- “, “,”))
This minor change accounts for data that might have a hyphenated name for, say, a city or job-role.

Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Reply | Quote