Stripping out imbedded spaces in a cell/row

[Guest]

Excel 2003
Is there a 'Strip' function in Excel 2003 so that I can strip out the
imbedded spaces between words/phrases that exist in one cell and put the
'compressed' result in another cell?
For example, if I have "Business Profile Stage 3" I'd like to 'strip' the
phrase to be 'BusinessProfileStage3". I just need to strip the spaces out,
nothing else (at this time..!!!). [Feature creep still exists!!]
I'm creating program variable names from useable phrases so the I can
understand what I did at a later date.
TIA,
Tom
[PS: Trim strips leading and trailing spaces but not imbedded. Using an
"AT" or "FIND" doesn't do it universally as I never know how many spaces I'll
need. Unless there's a creative way to iteratively find all spaces and
remove them.]

 

[Guest]

Hi

Try Find/Replace. Put a space in the Find field, and leave the Replace field
blank.

 

[Peo Sjoblom]

You could replace space with nothing under edit>replace or use

=SUBSTITUTE(A1," ","")

 

[Guest]

Have you tried the SUBSTITUTE function?
=SUBSTITUTE(A1," ","")

The above formula reads the text from cell A1 and replaces all spaces with
nothing ("").

OR

Can you just copy the cells to another column and use Edit>Replace to remove
all spaces?

Does that help?
Ron

 

[Bernie Deitrick]

Tom,

In a spreadsheet, use the SUBSTITUTE function:

=SUBSTITUTE(A1," ","")

In VBA, you would use the Replace function:

myVar = Replace(Range("A1").Value, " ", "")
MsgBox myVar

HTH,
Bernie
MS Excel MVP

 

[Guest]

Here's what I think you're saying:
=REPLACE(FIND(" ",B18),1,LEN(B18),)
It didn't work so I'm unable to see what you're driving at.
Thanks,
Tom

Hi

Try Find/Replace. Put a space in the Find field, and leave the Replace field
blank.

--
Andy.

Excel 2003
Is there a 'Strip' function in Excel 2003 so that I can strip out the
imbedded spaces between words/phrases that exist in one cell and put the
'compressed' result in another cell?
For example, if I have "Business Profile Stage 3" I'd like to 'strip' the
phrase to be 'BusinessProfileStage3". I just need to strip the spaces
out,
nothing else (at this time..!!!). [Feature creep still exists!!]
I'm creating program variable names from useable phrases so the I can
understand what I did at a later date.
TIA,
Tom
[PS: Trim strips leading and trailing spaces but not imbedded. Using an
"AT" or "FIND" doesn't do it universally as I never know how many spaces
I'll
need. Unless there's a creative way to iteratively find all spaces and
remove them.]

 

[Guest]

Hi
What I meant was to use Edit/Find . . Replace.!

--
Andy.

Here's what I think you're saying:
=REPLACE(FIND(" ",B18),1,LEN(B18),)
It didn't work so I'm unable to see what you're driving at.
Thanks,
Tom

Hi

Try Find/Replace. Put a space in the Find field, and leave the Replace
field
blank.

--
Andy.

Excel 2003
Is there a 'Strip' function in Excel 2003 so that I can strip out the
imbedded spaces between words/phrases that exist in one cell and put
the
'compressed' result in another cell?
For example, if I have "Business Profile Stage 3" I'd like to 'strip'
the
phrase to be 'BusinessProfileStage3". I just need to strip the spaces
out,
nothing else (at this time..!!!). [Feature creep still exists!!]
I'm creating program variable names from useable phrases so the I can
understand what I did at a later date.
TIA,
Tom
[PS: Trim strips leading and trailing spaces but not imbedded. Using
an
"AT" or "FIND" doesn't do it universally as I never know how many
spaces
I'll
need. Unless there's a creative way to iteratively find all spaces and
remove them.]

 

[Guest]

Hi
Having re-read your post, I think I'm barking up the wrong tree! You want
the original cell value and the new cell value. I was getting rid of the
spaces in the existing cells.

--
Andy.

Hi
What I meant was to use Edit/Find . . Replace.!

--
Andy.

Here's what I think you're saying:
=REPLACE(FIND(" ",B18),1,LEN(B18),)
It didn't work so I'm unable to see what you're driving at.
Thanks,
Tom

Hi

Try Find/Replace. Put a space in the Find field, and leave the Replace
field
blank.

--
Andy.


Excel 2003
Is there a 'Strip' function in Excel 2003 so that I can strip out the
imbedded spaces between words/phrases that exist in one cell and put
the
'compressed' result in another cell?
For example, if I have "Business Profile Stage 3" I'd like to 'strip'
the
phrase to be 'BusinessProfileStage3". I just need to strip the spaces
out,
nothing else (at this time..!!!). [Feature creep still exists!!]
I'm creating program variable names from useable phrases so the I can
understand what I did at a later date.
TIA,
Tom
[PS: Trim strips leading and trailing spaces but not imbedded. Using
an
"AT" or "FIND" doesn't do it universally as I never know how many
spaces
I'll
need. Unless there's a creative way to iteratively find all spaces
and
remove them.]

 

[Guest]

Thanks to all who jump in... I guess I didn't check the SUBSTITUTE function
because my brain wanted a "STRIP" function. Oh well.. glad it's Friday!

Tom,

In a spreadsheet, use the SUBSTITUTE function:

=SUBSTITUTE(A1," ","")

In VBA, you would use the Replace function:

myVar = Replace(Range("A1").Value, " ", "")
MsgBox myVar

HTH,
Bernie
MS Excel MVP

Excel 2003
Is there a 'Strip' function in Excel 2003 so that I can strip out the
imbedded spaces between words/phrases that exist in one cell and put the
'compressed' result in another cell?
For example, if I have "Business Profile Stage 3" I'd like to 'strip' the
phrase to be 'BusinessProfileStage3". I just need to strip the spaces out,
nothing else (at this time..!!!). [Feature creep still exists!!]
I'm creating program variable names from useable phrases so the I can
understand what I did at a later date.
TIA,
Tom
[PS: Trim strips leading and trailing spaces but not imbedded. Using an
"AT" or "FIND" doesn't do it universally as I never know how many spaces I'll
need. Unless there's a creative way to iteratively find all spaces and
remove them.]