+ Reply to Thread
Results 1 to 14 of 14

Parse text string based on unique input

  1. #1
    Registered User
    Join Date
    07-25-2014
    Location
    Oftringen, Switzerland
    MS-Off Ver
    Professional Plus 2010
    Posts
    16

    Parse text string based on unique input

    Hello all,

    In my excel worksheet, I have a column that contains text and I need to split the text input into multiple columns such as Name, Address, Postcode and Town.
    Unfortunately, the name and address and town part of the text string are uneven and only the post code has a unique format i.e. only 5 numbers, I would like to be split the text string into different columns, as shown in the excel attached.
    Does anyone know how to achieve this?

    Kind regards
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Parse text string based on unique input

    For the examples you have chosen, please show the desired result in ALL cases, as I'm not sure what it should be (esp for row 4).
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Parse text string based on unique input

    Kludgy, but seems to work

    B2: =TRIM(LEFT(SUBSTITUTE(A2," ",REPT(" ",100)),(LEN(LEFT(A2,FIND(".",A2)))-LEN(SUBSTITUTE(LEFT(A2,FIND(".",A2))," ","")))*100))
    C2: =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,B2,""),D2,""),E2,""))
    D2: =TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A2,E2,""))," ",REPT(" ",100)),100))
    E2: =TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",100)),100))

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Parse text string based on unique input

    Bob, I assume that your avatar is wee Geordie Best?? The same image is used on a pub sign in downtown Bangkok ("The Pickled Liver" - I wonder why??).

  5. #5
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Parse text string based on unique input

    Quote Originally Posted by Glenn Kennedy View Post
    Bob, I assume that your avatar is wee Geordie Best?? The same image is used on a pub sign in downtown Bangkok ("The Pickled Liver" - I wonder why??).
    That's just cruel

    Never would call him a Geordie though.

  6. #6
    Registered User
    Join Date
    07-25-2014
    Location
    Oftringen, Switzerland
    MS-Off Ver
    Professional Plus 2010
    Posts
    16

    Re: Parse text string based on unique input

    Quote Originally Posted by Glenn Kennedy View Post
    For the examples you have chosen, please show the desired result in ALL cases, as I'm not sure what it should be (esp for row 4).
    Hi Glen

    Thank you for your reply.Please find attached the updated list with the required output.

    Thank you
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    07-25-2014
    Location
    Oftringen, Switzerland
    MS-Off Ver
    Professional Plus 2010
    Posts
    16

    Re: Parse text string based on unique input

    Hi Bob

    I tried your suggestion and it works perfectly for column B2 and C2, however, things go haywire on column D2 and E2.

    Additionally, with B2 and C2, when A2 isnt in the format Text."Space"Digit, I get a null result on B2 and C2, how can I be able to run a formula that checks for this format Text."Space"Digit and append a "." after the text?

    Kind regards

  8. #8
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Parse text string based on unique input

    Can you give examples of the input and what you get as output in those cases?

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Parse text string based on unique input

    I don't see your problem with Bob's solution. It looks perfect to me..
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    07-25-2014
    Location
    Oftringen, Switzerland
    MS-Off Ver
    Professional Plus 2010
    Posts
    16

    Re: Parse text string based on unique input

    I see the errors in my list, my actual list has phone numbers in the text string.

  11. #11
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Parse text string based on unique input

    Then can you please post something that is more representative of your actual data?

  12. #12
    Registered User
    Join Date
    07-25-2014
    Location
    Oftringen, Switzerland
    MS-Off Ver
    Professional Plus 2010
    Posts
    16

    Re: Parse text string based on unique input

    Hi Glen

    Please find attached a correct representation of my data including the expected results.
    Attached Files Attached Files

  13. #13
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,891

    Re: Parse text string based on unique input

    Between your first set of examples and the second; in addition to the phone numbers, two other significant changes have occurred (removal of two . and inclusion of "Bruno"). Were these additional changes intentional? If so, it's back to the drawing board. If not, then a slight tweak to Bob's solution will work perfectly.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    07-25-2014
    Location
    Oftringen, Switzerland
    MS-Off Ver
    Professional Plus 2010
    Posts
    16

    Re: Parse text string based on unique input

    Hi Glenn

    This set of data is more representative of my data.
    Not all data has been delimited with (.) and some of the names contain more than 3 names,such as the addition of Bruno to the name.

    Looking forward to hearing from you.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Truncating a text string based off of two unique criteria
    By AlphaSkidz in forum Access Tables & Databases
    Replies: 6
    Last Post: 02-14-2014, 11:03 AM
  2. [SOLVED] Preparing text string based upon check box input in a userform
    By rreifs68 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-28-2013, 12:31 AM
  3. How to parse a simple text string with VBA?
    By barryleajo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-29-2010, 06:33 AM
  4. [SOLVED] How do i parse a text string with a date?
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-13-2006, 02:02 AM
  5. [SOLVED] Parse a space delimited string into unique columns
    By erighter in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-10-2005, 09:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1