Need to add not remove ","

[bubba100]

I am editing some information before it is exported to a db. There are 9000 records with city states shown as LouisvilleKy. I need them to show Louisville,KY. How can I add a comma? Doing a search shows how to remove characters but not add. Thanks in advance.

 

[genomon]

strName = Mid(strName,1,Len(strName) - 2) & ", " _
& Right(strName,2) will work if the state part is always two characters at the end of the name.

I have great faith in fools; self-confidence my friends call it.
-Poe

 

[PHV]

strNew = Left(strOld, Len(strOld) - 2) & "," & UCase(Right(strOld, 2))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AnotherHiggins]

Are the states always listed as a two-letter abbreviation?

If so, then you can simply use this formula (there's no need for VBA):
[tab][COLOR=blue white]=left(A1,len(A1)-2)&", "&right(A1,2)[/color]

But it would be much, much better design to have city and state separated into two different fields in your database. To accomplish this, use these two formulas:
[tab][COLOR=blue white]=left(A1,len(A1)-2)[/color]
and
[tab][COLOR=blue white]=right(A1,2)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bubba100]

Thanks for the FAST response.