string manipulation

[keith05281967]

Greetings,

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 city and the 2 digit state. I think i can make this work if I can start from the right of the cell, then move 2 spaces just to the left of the 2 digit XX state abbv. then going right to left count the number of spaces. If < 3 spaces then add 3 spaces.

Can this be done?

Excel '07

thanks,
Keith

 

[MrKowz]

Try having your list in column A, and use the following formula:

=LEFT(SUBSTITUTE(A1," ",""),LEN(SUBSTITUTE(A1," ",""))-2)&[COLOR=red][B]"   "[/B][/COLOR]&RIGHT(A1,2)

Note that the red-highlighted area needs to be 3 spaces.

 

[VoG]

Try

=LEFT(A1,LEN(A1)-2)&REPT(" ",3-(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))&RIGHT(A1,2)

 

[Ron Coderre]

Try this regular formula:

=TRIM(LEFT(A1,LEN(A1)-2))&"   "&RIGHT(A1,2)

Is that something you can work with?

 

[mikerickson]

Perhaps
=REPLACE(SUBSTITUTE(E3," ",""), LEN(SUBSTITUTE(E3," ",""))-1, 0, " ")

 

[keith05281967]

My apologies for not being clear. Can you state this same thing in vba code?

what is the equivalent of this:
=LEFT(A1,LEN(A1)-2)&REPT(" ",3-(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))&RIGHT(A1,2)

in vba code?

thanks again,
Keith