Semicolons need to be moved!PLZ help

[MavRiK]

Hi,

Can someone plz help:
Ive imported data from a text file into an excel sheet
The data is of the format: Company;Addr1;Addr2;Phone
the text file was created with errors in, some of the addresses are
split by semicolon, sample : SomeCompany;Jason;Park;Washington 123 22
3333
and SomeCompany;Jason;Park;New York 123 222 3333
As you can see the last 2 semicolons need to be moved:
SomeCompany;Jason Park;Washington;123 22
3333
SomeCompany;Jason Park;New York;123 22
3333

I would appreciate the help.
Thanks in advance.
Pete

 

[Sandy Mann]

MavRiK

If your data is not in separate cells, ie SomeCompany;Jason;Park;New York
123 222 3333 is all in one cell, then try Edit > Replace and in "Find what:"
enter the semicolon and a comma in "Replace with:"

HTH

Sandy

 

[Ken Wright]

That would remove ALL the semicolons though and not just the last 2 as
requested.

With data in Col A starting A2, in B2 put the following and copy down:-

=SUBSTITUTE(SUBSTITUTE(A2,";",",",LEN(A2)-LEN(SUBSTITUTE(A2,";",""))),";",",",LEN(A2)-LEN(SUBSTITUTE(A2,";",""))-1)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

MavRiK

If your data is not in separate cells, ie SomeCompany;Jason;Park;New York
123 222 3333 is all in one cell, then try Edit > Replace and in "Find what:"
enter the semicolon and a comma in "Replace with:"

HTH

Sandy

 

[Ken Wright]

If of course you don't want to replace with commas and simply want spaces, then
amend to

=SUBSTITUTE(SUBSTITUTE(A2,";"," ",LEN(A2)-LEN(SUBSTITUTE(A2,";",""))),";","
",LEN(A2)-LEN(SUBSTITUTE(A2,";",""))-1)

Again, doesn't matter how many semiscolons (As long as at least 2), it will
always remove the last 2.