text manipulation

[keith05281967]

Greetings All,

I posted this question yesterday but failed to mention that i'm looking for a vba solution. I did get some great answers but they were in the form of worksheet functions =LEFT(A1,LEN(A1)-2)&REPT(" ",3-(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))&RIGHT(A1,2)

I have cells with data like this: "Houston TX " or "Phoenix AZ " but occasionally it comes in like "AustinTX " with no spacing.

In order to pass the database test these cells should have at least 3 spaces between the city and the 2 digit state. I think the logic to make this work is to start from the far right of the cell, remove any trailing spaces, then move 2 spaces left so the focus is to the immediate left of the 2 digit TX state abbv. then going right to left count the number of spaces. If < 3 spaces then add 3 spaces.

It would loop thru a column of data doing this.

Can this be done in vba code?

Excel '07

thanks,
Keith
<!-- / message -->

 

[VoG]

Maybe like this

Sub test()
With Range("A1")
    .Value = Evaluate("=LEFT(A1,LEN(A1)-2)&REPT("" "",3-(LEN(A1)-LEN(SUBSTITUTE(A1,"" "",""""))))&RIGHT(A1,2)")
End With
End Sub

 

[njimack]

Assuming your data starts from A2...

Sub a()
Dim Last_Row As Long
Dim i As Long
Dim s As String

Application.ScreenUpdating = False

Last_Row = Range("A" & Rows.Count).End(xlUp).Row

For i = 2 To Last_Row
    s = Cells(i, 1).Value
    s = Trim(s)
    s = Left(s, Len(s) - 2) & "   " & Right(s, 2)
    Cells(i, 1).Value = s
Next i

Application.ScreenUpdating = True

End Sub

 

[keith05281967]

Those both work equally well. Thanks so much for your quick feedback.

Well done!

Keith