+ Reply to Thread
Results 1 to 5 of 5

How do I split a string?

  1. 01-01-2021, 11:38 AM #1
    MoeFoxx
    MoeFoxx is offline
    Registered User
    Join Date
    11-26-2020
    Location
    Miami
    MS-Off Ver
    18
    Posts
    13

    Question How do I split a string?

    I need to split the address from this string. Some strings have 2 delimiters before getting to the address and some have one depending on the number of names. So I started from the right side. I guess there would be a way to start from the left and find the first number also.


    ALVES HERMINIA M | 316 CLEVELAND AVE | HARRISON, NJ 07029


    BARBOSA MANUEL J AND | MARIA C BARBOSA | 59 LANG STREET | NEWARK, NJ 07105


    I was able to get the city, state and zip by using this formula starting from the right side. I don't know how to get the address though. If I could go to the next “|” and subtract the city,st and zip? Or just get the data between the last and second to last “|”.


    =TRIM(RIGHT(SUBSTITUTE(TRIM(A1), "|", REPT(" ", 99)), 99))
    Reply With QuoteRegister To Reply
  2. 01-01-2021, 11:50 AM #2
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: How do I split a string?

    Please try

    =TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(A1), "|", REPT(" ", 99)), 99*2),99))
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  3. 01-01-2021, 11:51 AM #3
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,301

    Re: How do I split a string?

    There are instructions at the top of the page explaining how to attach your sample workbook.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  4. 01-01-2021, 01:22 PM #4
    MoeFoxx
    MoeFoxx is offline
    Registered User
    Join Date
    11-26-2020
    Location
    Miami
    MS-Off Ver
    18
    Posts
    13
    Quote Originally Posted by Bo_Ry View Post
    Please try

    =TRIM(LEFT(RIGHT(SUBSTITUTE(TRIM(A1), "|", REPT(" ", 99)), 99*2),99))
    Thank you that worked great.
    Reply With QuoteRegister To Reply
  5. 01-01-2021, 01:24 PM #5
    alansidman
    alansidman is offline
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 insider Version 2504 Win 11
    Posts
    24,688

    Re: How do I split a string?

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Split comma delimited string on comma replace comma with pipe then rejoin Join string
    By capson in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-25-2020, 01:12 AM
  2. Split from string
    By tax112 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-30-2018, 10:59 AM
  3. [SOLVED] Split string and apply function on string parts
    By fandreacci in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-25-2015, 12:34 PM
  4. [SOLVED] Split String at every "/" then put split string results into a multiline textbox.
    By Andrew Andromeda in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-01-2015, 01:49 AM
  5. How to split a string based on a sub string
    By dippa in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 11-24-2014, 08:04 PM
  6. Advanced Split Function: Need to pull text string in quotes, within a text string
    By Zamboni in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 11-27-2013, 04:38 PM
  7. Can excels Split() function split a string up at multiple spots?
    By 111StepsAhead in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-14-2011, 02:36 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1