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