Inserting/Deleting Spaces

C

carl

I have 5 character data that comes in with 0, 1, and 2 spaces.
I need to convert this data like so:

X__QH I need X_QH (2 Spaces replaced with 1 Space)
GS_EB I need GS_EB (1 Space No Change)
GOUQT I need GOU_QT (No Space, after 3rd Character insert 1 Space)

Is there a way to accomplish this ?

Thank you in advance.
 
J

JulieD

Hi Carl

does
=IF(COUNTIF(A1,"* *")=1,SUBSTITUTE(A1," "," "),IF(COUNTIF(A1,"*
*")=0,LEFT(A1,3) & " " & RIGHT(A1,LEN(A1)-3),A1))

give you what you need?
 
O

Otto Moehrbach

You can use the worksheet function Trim to reduce the 2 spaces to one space.
Look in Help under Trim.
To add a space as in your last example would depend on the pattern that your
data has. For instance, can you say that you want to add a space after the
third character of each entry that has 5 characters and no spaces? HTH
Otto
 
C

carl

Thank you JulieD. I think it gets me close to what I need. I think if A1 has
only 1 space in it, the formula is inserting a 2nd. I am hoping that if A1
has only 1 space, to leave the cell unchanged.

Maybe I am putting the formula into excel incorrectly ?

To summarize, I am trying to get the following result:

If A1 has 0 Spaces, insert a space in between the 3rd and 4th character
If A1 has 1 space, leave the cell unchanged
If A1 has 2 spaces, remove one of the spaces

Thank you again for you help.
 
J

JulieD

Hi Carl

if i test it using the len(A1) function to count the number of characters
before and after applying this formula it does not show any change in the
length if there was 1 space before ...
old..........formula...............len(old).....len(formula)
the cat.... the cat ..................8................. 7
the cat .....the cat...................7..................7
thecat ......the cat...................6..................7


note, when testing the formula from my post i did have to retype (for some
reason) the spaces so here's the formula in words ...
=IF(COUNTIF(A1,"*<space><space>*")=1,SUBSTITUTE(A1,"<space><space>","<space>"),IF(COUNTIF(A1,"*<space>*")=0,LEFT(A1,3)
& "<space>" & RIGHT(A1,LEN(A1)-3),A1))
 
Top