• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Mid and Replace

J

Joseph Horling

Member
Hi Chandoo,
I would like to use this formula: 66413 to find the other spaces in my text. There is about 8 spaces in my text and I would like to extract the text from each space. Not sure how to find the other spaces. I want to get the last name, street no, street name, city, state, and zip separate. Do I use more finds? Hope I explained this correctly. Attached is my file. Thank you for your help, Joe from Michigan.
 

Attachments

Use FilterXML function for this sort of thing.

To start, you can extract each string between space(s).
In B2:
=INDEX(FILTERXML("<a><b>"&SUBSTITUTE($A2," ","</b><b>")&"</b></a>","//b"),COLUMN(A1))

Copy across till you see #REF error and then down. You can use IFERRROR(formula, "") if you don't want #REF to show up.

Exactly how address string should be dealt will depend on patterns that show up in sample. Since not all address follow same pattern.

Ex: Code logic can't tell where to break for town/city.

In these cases... best bet is to leverage Web Search Engines to get attributes other than name.

I.E. Using VBA to search for address string, then scrape result for each attribute.
66417
 
You can also try following approach. Copy formula in cell B2:
=TRIM(MID(SUBSTITUTE(" "&$A2," ",REPT(" ",99)),COLUMN(A2)*99,99))
copy down and across!
 
Hi Shrivallabha,

It's almost what I wanted. Looking to get Jamestown Circle /Slaton Drive /North Silver Pines Drive in column f. Also need Grand Haven in column H. And no comas after Northville Grand Haven and Bellaire. I applied the formula in this file for you to look at to see what I mean. Thank you so much for getting me this far. I appreciate your help. Joe.
 

Attachments

Hi Joseph,

What I have posted is fairly straightforward part of parsing the text string. However, it will be difficult to find a suitable formula expression which deals with all different cases (as number of cases and words vary).

If you are able to post a clear logic for achieving this then a formula might be possible else it will be wild goose chase!
 
You must log in or register to reply here.
Back
Top