Semicolon problem

[MavRiK]

Hi,

I've got some data which i imported from a text file
The data constains company names adrresses and telephone numbers
Now the problem is, in the text file some of the semicolons are in th
wrong place.
When i import it into excel i want to move the semicolon so that space
belongs where they must, example

imported into excel: (Semicolon where space should be)
TFS Securities (Pty) Ltd;Sandton;Gauteng;011 465 3686
Vector Equities (Pty) Ltd;Cape*;*Town;Western Cape;021 419 3992
Watermark Securities (Pty) Ltd;Hyde*;*Park;Gauteng;011 325 4228
Cazenove South Africa (Pty) Ltd;Dunkeld;West*;*Gauteng;011 280 7900

The first one is correct but the last 3 needs changing:
Vector Equities (Pty) Ltd;Cape Town;Western Cape;021 419 3992
Watermark Securities (Pty) Ltd;Hyde Park;Gauteng;011 325 4228
Cazenove South Africa (Pty) Ltd;Dunkeld;West Gauteng;011 280 7900

As you can see this is a dillemma.
I'm trying to automate the change with a macro, but some of the length
clash and then i had a space where it doesnt belong.
I would appreciate the help.
Thanks in advance.
Pete

 

[Tom Ogilvy]

Sub ReplaceData()
Cells.Replace What:="~*;~*", _
Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End sub

Test it on a copy of your data.

 

[Martin]

From your sample, it looks like you always have a "*;*"
where you want only a ";". I would do a search and
replace on the original text file (or on a copy of it)
before the import to Excel using the ";" as the separator
character.

It may be worth trying to determine why the system that is
producing this text file is adding those extra characters.

Hope this helps.

-----Original Message-----

Hi,

I've got some data which i imported from a text file
The data constains company names adrresses and telephone numbers
Now the problem is, in the text file some of the semicolons are in the
wrong place.
When i import it into excel i want to move the semicolon so that spaces
belongs where they must, example

imported into excel: (Semicolon where space should be)
TFS Securities (Pty) Ltd;Sandton;Gauteng;011 465 3686
Vector Equities (Pty) Ltd;Cape*;*Town;Western Cape;021 419 3992
Watermark Securities (Pty) Ltd;Hyde*;*Park;Gauteng;011 325 4228
Cazenove South Africa (Pty)

Ltd;Dunkeld;West*;*Gauteng;011 280 7900