Semicolon problem

[MavRiK]

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

 

[PBezucha]

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

 

[Tom Ogilvy]

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