Stripping out imbedded spaces in a cell/row
 

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.]

Hi

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

--
Andy.


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

You could replace space with nothing under editreplace or use

=SUBSTITUTE(A1," ","")

--
Regards,

Peo Sjoblom


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

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 EditReplace to remove
all spaces?

Does that help?
Ron

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


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

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

"Andy B" wrote:

Hi

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

--
Andy.


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

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

--
Andy.


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

"Andy B" wrote:

Hi

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

--
Andy.


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

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.


<Andy B wrote in message ...
Hi
What I meant was to use Edit/Find . . Replace.!

--
Andy.


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

"Andy B" wrote:

Hi

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

--
Andy.


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

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!
:-)

"Bernie Deitrick" wrote:

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


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