Extracting Text From Cells of Variable Lengths

[stinkstik]

Hello, let me show you what I'm up against...

A:
1: AURORA, OH 44202 USA BEACHWOOD, OH 44122 USA BEACHWOOD, OH 44122 USA
2: MENTOR, OH 44060 USA BEACHWOOD, OH 44122 USA AVON, OH 44011 USA


I would like to extract the final 'City, State, Zip, USA" from each cell 'A' and put it in cell B. But BEACHWOOD and AVON are different lengths. Any suggestions?

Thanks so Much!
stinkstik

[Neil_]

Copy column A to column B.
select column B
Data>text to columns
Delimited by comma
delete columns B:D

[stinkstik]

Hey Neil_,

Thanks so much for taking the time to reply, but delimiting by comma and deleting cells B and D just leaves me with a result of " OH 44077 USA | GATES MILLS OH 44122 USA". Maybe I can rephrase...

I would like to receive a final result of "BEACHWOOD, OH 44122 USA" all in one cell. I would like the comma included with data on either side, therefore I can't delimit by comma. I have a list of thousands of addresses of different lengths so I can not delimit by one single factor. Therefore a function is needed.

Thanks!

[Neil_]

Does the last instance of 'USA' always immediately precede the data you are after? Or should I say the second one?

[stinkstik]

Yes. There are always 3 instances of 'USA'. "City, State Zip USA City, State Zip USA City, State Zip USA". I've been trying to combine the RIGHT function with SEARCH or FIND functions but so far to no avail. But I'm probably missing a step.

I appreciate your time!

[Neil_]

If the address always look like this:-


someplace USA some other part of address USA the end bit Im after USA

then select the entire column, press CTRL H and substitute USA with a character you know isn't in the list such as ^ or %. then delimit by that character. You can always concatenate " USA" to the end of the addresses afterwards

[oeldere]

First you use the solution of Neil (in #2)

After that you can collect the desired outcome in one cell e.g. column Z
e.g.=> = a1 & ", " & c1

[AZ-XL]

Hi
For each criteria I used seperate formulas
city: =TRIM(MID(SUBSTITUTE(A2,"USA","#",2),FIND("#",SUBSTITUTE(A2,"USA","#",2))+1,FIND(",",SUBSTITUTE(A2,"USA","#",2),FIND("#",SUBSTITUTE(A2,"USA","#",2)))-FIND("#",SUBSTITUTE(A2,"USA","#",2))-1))

State =LEFT(A2,FIND(",",A2)-1)

Zip =MAX(IFERROR(1*MID(A2,MIN(SEARCH({0;1;2;3;4;5;6;7;8;9},LEFT(A2,FIND("USA",A2))&"0123456789")),ROW($1:$100)),0))

USA =RIGHT(A2,3)


Uploading file

[stinkstik]

Hey AZ-XL,

Thanks for your reply. That looks pretty intense! I will definitely study your solution and try to learn from it.

Thanks again!

[AZ-XL]

Hey AZ-XL,

Thanks for your reply. That looks pretty intense! I will definitely study your solution and try to learn from it.

Thanks again!

Your are welcome.

[stinkstik]

Neil_ you are a genius!!!!! I had no idea about that CTRL H function! It seems so simple! Thanks so much!

[Neil_]

Glad it worked for you!