Text to Column Not Working

Hello

Please check the attached spreadsheet

I am trying to split the addresses in street name, city, state and ZIP but when I am trying to do a Text-to-Column (using comma as the delimiter), only the first row of the address is appearing

Kindly advise

Thanks

Update - I have figured out the issue.

Solved the formatting problem with the following code

Please Login or Register  to view this content.

However, the city name is now stuck to the street name like 2361 Camino PintoresSanta Fe, NM 87505-5290 and I am not sure how I can split the city name (Santa Fe in this case to a new column)

More such address examples are

PO Box 3170Albuquerque, NM 87190-3170
5919 Tierra Viva Pl NWAlbuquerque, NM 87107-5275
924 Park Ave SW Ste CAlbuquerque, NM 87102-3023
405 W Congress St Ste 4800Tucson, AZ 85701-5040
PO Box 30181Albuquerque, NM 87190-0181

Kindly advise

Thanks

Originally Posted by AliGW

Why didn't you do the substitution with a space " " instead of a blank ""?

Hi AliGW

Thanks a lot for the tip. I am not good with reading codes and so didn't think of replacing the blank with a space. After I use a space, the cells look much neat but I am still not sure how I can separate the city name in a column as there is no delimiter before the city name, except for space, which would split up all the words in the address

This is how the addresses look now

2361 Camino Pintores Santa Fe, NM 87505-5290
PO Box 3170 Albuquerque, NM 87190-3170
5919 Tierra Viva Pl NW Albuquerque, NM 87107-5275
924 Park Ave SW Ste C Albuquerque, NM 87102-3023
405 W Congress St Ste 4800 Tucson, AZ 85701-5040
PO Box 30181 Albuquerque, NM 87190-0181
2931 Nebraska St Sioux City, IA 51104-3654
760 N Motel Blvd Ste A Las Cruces, NM 88007-4169
PO Box 1750 Bernalillo, NM 87004-1750
131 M St NE Washington, DC 20507-0003
PO Box 25102 Santa Fe, NM 87504-5102
4022 Flamingo Dr El Paso, TX 79902-1314
3200 Carlisle Blvd NE Ste 219 Albuquerque, NM 87110-1699
PO Box 8814 Santa Fe, NM 87504-8814

Could you please give an idea how I can split the city name in a new column?

Thanks again

Hello, your text does not match text written in formula box, that is your problem i guess.

aaaaaaaaaa.png'

I'm able to respond in real time to you until its solved.

Originally Posted by AliGW

I am so sorry - I meant to say substitute the line break character with a comma, then you can use the comma as planned as the delimiter.

No problem, AliGW. I replaced the blank with a comma and it worked just like the way I wanted. Thank you sooo much!!

Originally Posted by ArnoldMS

Hello, your text does not match text written in formula box, that is your problem i guess.

Attachment 494521'

I'm able to respond in real time to you until its solved.

ArnoldMS - thank you so much for your help. I was able to solve the issue using the following code

Please Login or Register  to view this content.

I appreciate you willing to stay with me till the issue was resolved.

You guys are the best!

Point noted, AliGW.

Thanks again