Last word from cell into new column

[clairer86]

After hours of fruitless searching, I've made up my mind to simply post my question. For work-related reasons, I need to take a series of single cells that contain addresses and split them all into 4 or 5 cells: street address, street address 2, city, state, and zip code. I've cobbled together macros that extract that zip code and state (surprisingly easy because they're always the same number of characters), and I would love to have a similar macro/method to extract the last word of the remaining aggregate address and put it in the "city" column. Obviously this wouldn't work 100 percent of the time, what with there being many cities with more than one word, but it would work a lot of the time and save me hours.

I hope that's clear, but if not, here's an example:

I get 1234 N Main St. Phoenix AZ 12345 in column A
"12345" gets moved to column E
"AZ" gets moved to column D
I would like "Phoenix" to move to column C

I'd like to note that I do understand (thanks to other posts here!) how to use a formula in column C to put the last word from column A in column C, but that I need "Phoenix" to no longer be in column A when I'm done -- and none of the formulas that I've come across seem to do this.

Your help is greatly appreciated!

 

[Michael M]

If you use the formulas to create the broken up address.....then copy/paste values to all formula columns
Now delete the original column


Have a look here for a series of formula to split addresses.....http://www.exceldashboardtemplates.com/the-best-way-to-separate-address-text-to-multiple-columns/

 

[István Hirsch]

With a formula you can change the value of the cell you are putting in the formula, but can not change the value of the original cell, so you can not delete Phoenix from A1 with a formula.

Enter in

B1:

=TRIM(SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",99)),RIGHT(SUBSTITUTE(A1," ",REPT(" ",99)),297),""))

C1:

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&$A1," ",REPT(" ",99)),(6-COLUMN())*99),99))

then drag the formula in C1 right through D1 and E1.

Excel Workbook
	A	B	C	D	E
1	1234 N Main St. Phoenix AZ 12345	1234 N Main St.	Phoenix	AZ	12345
Sheet

 

[clairer86]

Thank you, both of you! I know I need more practice with this sort of thing ...

MichaelM -- Unfortunately, the addresses I receive are created manually by a hundred people who want to format addresses differently; I don't have a convenient delimiter like a comma that I can rely on every time for text-to-columns (or I would be able to get this done instantly). And it looks like a delimiter is what this method relies on?

István Hirsch -- I was stuck in the mindset that I didn't want to be creating any extra columns, but so far I like your solution best. It does almost exactly what I want it to do. Thank you!

 

[clairer86]

Actually, I was wrong. The last solution works elegantly for 1234 N Main St. Phoenix AZ 12345 -- but it does not work for 1234 N Main St. New Jack City AZ 12345.

Is there a way to implement this and separate only last word from everything but the last word, regardless of the number of words? (I'm reading up on the TRIM function, but I'm not getting quite where I want.)

 

[István Hirsch]

Actually, I was wrong. The last solution works elegantly for 1234 N Main St. Phoenix AZ 12345 -- but it does not work for 1234 N Main St. New Jack City AZ 12345.

Is there a way to implement this and separate only last word from everything but the last word, regardless of the number of words? (I'm reading up on the TRIM function, but I'm not getting quite where I want.)

Please show me the required separation of your second sample, where the formula does not work.

 

[clairer86]

Sorry, I had words disappearing on me and thought it involved the function -- in fact the addresses had extraneous spaces. It works perfectly when the words are spaced normally. Thanks again!

 

[István Hirsch]

Sorry, I had words disappearing on me and thought it involved the function -- in fact the addresses had extraneous spaces. It works perfectly when the words are spaced normally. Thanks again!

Do not remove the extra spaces manually, try these formulas instead:

B1:

=TRIM(SUBSTITUTE(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),297),""))

C1:

=TRIM(LEFT(RIGHT(SUBSTITUTE(" "&TRIM($A1)," ",REPT(" ",99)),(6-COLUMN())*99),99))

Excel Workbook
	A	B	C	D	E
1	1234 N Main St. Phoenix AZ 12345	1234 N Main St.	Phoenix	AZ	12345
2	1234 N Main St. Phoenix AZ 12345	1234 N Main St.	Phoenix	AZ	12345
Sheet

 

[clairer86]

Works like a dream; brilliant, and very much appreciated.

 

[István Hirsch]

You are welcome, thanks for the feedback.