I am learning Excel formulas and this place has been amazing.
I have 4 city, state, zip formats I am trying to make uniform. I do not have the source of the city, state, and zip so I can't run a simple formula to combine via concatenating.
I want all of them to be City, State Zip -- five digits
Four Formats I am trying to manipulate:
Richmond, VA 23230
Richmond, VA 23230-5595
Richmond, VA, 23230
Richmond, VA, 23230-5595
What I have so far
I am able to easily pull out the nine-digit zip codes using the below formula:
=IFERROR( LEFT([@[Owner City State Zip2]],FIND("-",[@[Owner City State Zip2]])-1),[@[Owner City State Zip2]])
After running the above, I am now left with two city, state, zip formats:
Richmond, VA 23230
Richmond, VA, 23230
Any suggestions for getting all of them formatted like number 1 where there is not an additional comma after the state abbreviation?
I can't use find and replace because the state is not always VA. My sheet could have 20 different states. And again, I don't have the source of the inputs so am not able to run a simple concatenation. I can use text to columns and concatenate but it doesn't seem efficient.
Desired Result
Thank you!
CodePudding user response：
From you given sample data it seems simple SUBSTITUTE() should work for you. Try-
=SUBSTITUTE(A2,",","",2)
CodePudding user response：
Where Coloumn A is Your Data and Coloumn B is the Formula.
=CONCATENATE(LEFT(A1;FIND(",";A1;1) 3);IF(ISERROR(FIND(",";RIGHT(A1;LEN(A1)-FIND(",";A1;1)-1);1));MID(RIGHT(A1;LEN(A1)-FIND(",";A1;1)-1);3;6);MID(RIGHT(A1;LEN(A1)-FIND(",";A1;1)-1);4;6)))
If your using Old version Excel, replace " ; " it to a comma