Thread: split information in column to other columns

Hello there, well im new to excel macros and i manage a lot of data so im tired of doing the same thing over an over.

Ok so I attached a small example of what i have and ill try to explain

column E
Primera 496","Zona Centro, 22800 Mexicali, Baja California

know i whant the info over the 2nd quote (,) to go to column F and the 3rd quote (,) data to go to column G

and if posible another macro that can delete quotes , and the small space that separates it from the word, sorry im asking to much.

forgive my language, and thanks.

in the attached file in the first 2 rows thats what i whant it to look, the rest its what i have,
the main problem is that the data changes country to country, state to state, city to city, etc. But the thing in common is the quotes.

I tryed the macro record on excel to see what code it uses to separate them, but the only thing that does is write the words on the cells.

Try this

Select Column E Then Text to Columns > Deliminated > Next > Delimiters check "comma" Text qualifier select {none} > Click Finish.

Then select the result and use Find/Replace to remove Quotes " and apostrophes ' (or do this first if you would prefer to)

e.g.
Find what: "
Replace with; (leave blank)
Press "Replace All"

Repeat with '

Hope this helps.

Okay Try this first.

Select Column E
then
Find what: "," (quote comma quote)
Replace with; "|" (quote pipe quote)
Press Replace all

Repeat with ',' (apostrophe comma apostrophe)

Then Text to Columns as above

Then with Column E
Find what: "|" (quote pipe quote)
Replace with; ", " (comma space)
Press Replace all

That should do it.

Text to Columns is a useful tool when combined with Replace for splitting strings.
Give it a try.

Happy to help.