Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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.] |
#2
|
|||
|
|||
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.] |
#3
|
|||
|
|||
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.] |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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.] |
#6
|
|||
|
|||
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.] |
#7
|
|||
|
|||
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.] |
#8
|
|||
|
|||
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.] |
#9
|
|||
|
|||
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.] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove spaces in text in excel | Excel Discussion (Misc queries) | |||
Sumproduct ... Empty Cells vs Spaces? | Excel Discussion (Misc queries) |