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.

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?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"carl" wrote in message
...
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.

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
"carl" wrote in message
...
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.

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.



"JulieD" wrote:

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?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"carl" wrote in message
...
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.

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).....le n(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,"*<spac e*")=0,LEFT(A1,3)
& "<space" & RIGHT(A1,LEN(A1)-3),A1))

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"carl" wrote in message
...
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.



"JulieD" wrote:

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?

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"carl" wrote in message
...
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.