Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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 <alt> while entering 0160 on the numeric keypad


    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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
    [MS MVP - Word]

  5. #5
    3 Star Lounger
    Join Date
    Jul 2007
    Location
    United States
    Posts
    220
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  6. #6
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,055
    Thanks
    2
    Thanked 417 Times in 346 Posts
    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(R eplace(oCel.Value, "(", ""), ")", ""), "-", ","), Chr(160), " "))
    to:
    StrArr = WorksheetFunction.Proper(Replace(Replace(Replace(R eplace(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
    [MS MVP - Word]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •