Pull from right and replace

[Doc203]

What I want to do is start from the right, find the 1st space and replace it with a comma in VBA.

The data looks like:

Anyones AUTO,INC.,DON,659 SOUTH HIGH ST,CORTLAND, SD 44410,333-633-3333

What I need it to do is put a comma after the state so I can parse it.

Thanks for any help!

 

[MrKowz]

Try:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">Anyones AUTO,INC.,DON,659 SOUTH HIGH ST,CORTLAND, SD 44410,333-633-3333</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Anyones AUTO,INC.,DON,659 SOUTH HIGH ST,CORTLAND, SD,44410,333-633-3333</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A2</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">A1," ",",",LEN(<font color="Red">SUBSTITUTE(<font color="Green">A1," ","%%"</font>)</font>)-LEN(<font color="Red">A1</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

 

[VoG]

Try like this

Sub a()
Dim s As String, i As Long
s = "Anyones AUTO,INC.,DON,659 SOUTH HIGH ST,CORTLAND, SD 44410,333-633-3333"
i = InStrRev(s, " ")
s = Left(s, i - 1) & "," & Right(s, Len(s) - i)
MsgBox s
End Sub

 

[Doc203]

Thanks guys - I need it to do several address's at a time VoG, maybe a loop? Good stuff!

 

[VoG]

Try this: select the range then run the macro

Sub a()
Dim s As String, i As Long, c As Range
For Each c In Selection
    s = c.Value
    i = InStrRev(s, " ")
    c.Value = Left(s, i - 1) & "," & Right(s, Len(s) - i)
Next c
End Sub

 

[Doc203]

You rock... works perfectly - I think you saved me about 4 hours of beating my head against the wall.