Sorry guys, when i created the thread i wanted to make the wron
semicolon BOLD, but instead it added a * at both sides of th
semicolon.Sorry.
What i wanted to say is:

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


OK, the semicolons are all the same no characters at the wron
one....so i have to go through the string and test whether the ; is a
the right place.
if it is then go to next cell, if not then replace with ;. I dont kno
how to test thje strings because ; are at differnt places. As see
above.



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

In such a case I would suggest importing the text file
with all the semicolons and doing the proper rearrangement
in the created xl worksheet, for example:

Sub Arrange()
Dim I As Long
I = 1
Do
If Not IsEmpty(Cells(I, 5)) Then
Cells(I, 3) = Cells(I, 3).Value & " " & Cells(I,
4).Value
Cells(I, 4).Value = Cells(I, 5).Value
Cells(I, 5).Value = Null
End If
I = I + 1
Loop Until IsEmpty(Cells(I, 1))
End Sub

PB

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

Sorry guys, when i created the thread i wanted to make
the wrong
semicolon BOLD, but instead it added a * at both sides of
the
semicolon.Sorry.
What i wanted to say is:

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


OK, the semicolons are all the same no characters at the
wrong
one....so i have to go through the string and test
whether the ; is at
the right place.
if it is then go to next cell, if not then replace
with ;. I dont know
how to test thje strings because ; are at differnt
places. As seen
above.



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

.

This allows you to make the call. Otherwise, you would need a list of place
names that the code could compare against to make its own decision.

Sub AAA()
Dim cell As Range
Dim sStr As String
For Each cell In Selection
sStr = cell.Value
If Len(sStr) - Len(Application.Substitute(sStr, ";", "")) 3 Then
iloc = InStr(1, sStr, ";", vbTextCompare)
iloc = InStr(iloc + 1, sStr, ";", vbTextCompare)
sStr1 = Left(sStr, iloc - 1) & " " & Right(sStr, Len(sStr) - iloc)
iloc = InStr(iloc + 1, sStr, ";", vbTextCompare)
sStr2 = Left(sStr, iloc - 1) & " " & Right(sStr, Len(sStr) - iloc)

msg = sStr & vbNewLine & vbNewLine & _
"1. " & sStr1 & vbNewLine & _
"2. " & sStr2 & vbNewLine & vbNewLine & _
"Yes for 1, No for 2, Cancel to skip:"
ans = MsgBox(msg, vbYesNoCancel)
If ans = vbYes Then
cell.Value = sStr1
ElseIf ans = vbNo Then
cell.Value = sStr2
End If
End If
Next
End Sub


--
Regards,
Tom Ogilvy

"MavRiK" wrote in message
...

Sorry guys, when i created the thread i wanted to make the wrong
semicolon BOLD, but instead it added a * at both sides of the
semicolon.Sorry.
What i wanted to say is:

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


OK, the semicolons are all the same no characters at the wrong
one....so i have to go through the string and test whether the ; is at
the right place.
if it is then go to next cell, if not then replace with ;. I dont know
how to test thje strings because ; are at differnt places. As seen
above.



Peter


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