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

--
MavRi
-----------------------------------------------------------------------
MavRiK's Profile: http://www.excelforum.com/member.php...fo&userid=1472
View this thread: http://www.excelforum.com/showthread.php?threadid=26504

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

Test it on a copy of your data.

--
Regards,
Tom Ogilvy


"MavRiK" wrote in 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

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 lengths
clash and then i had a space where it doesnt belong.
I would appreciate the help.
Thanks in advance.
Peter


--
MavRiK
------------------------------------------------------------------------
MavRiK's Profile:
http://www.excelforum.com/member.php...o&userid=14728
View this thread: http://www.excelforum.com/showthread...hreadid=265047

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

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 lengths
clash and then i had a space where it doesnt belong.
I would appreciate the help.
Thanks in advance.
Peter


--
MavRiK
----------------------------------------------------------
--------------
MavRiK's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=14728
View this thread:
http://www.excelforum.com/showthread...hreadid=265047

.