|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
April 22nd, 2005, 02:03 PM
|
|||
|
|||
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
April 22nd, 2005, 02:41 PM
|
|||
|
|||
|
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
April 22nd, 2005, 02:48 PM
|
|||
|
|||
|
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
April 22nd, 2005, 02:49 PM
|
|||
|
|||
|
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
April 22nd, 2005, 02:50 PM
|
|||
|
|||
|
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
April 22nd, 2005, 03:01 PM
|
|||
|
|||
|
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
April 22nd, 2005, 03:25 PM
|
|||
|
|||
|
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
April 22nd, 2005, 03:34 PM
|
|||
|
|||
|
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
April 22nd, 2005, 03:49 PM
|
|||
|
|||
|
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.]
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| How do I get Word to stop putting extra spaces after a period whe. | Wants a Manual I Can Hold In My Hand! | General Discussion | 2 | February 2nd, 2005 12:27 AM |
| remove spaces in text in excel | GnarlyCar | General Discussion | 3 | February 1st, 2005 05:02 PM |
| Spaces in Attachment Names | Now_Using_MS | General Discussion | 3 | August 26th, 2004 06:15 PM |
| Spaces not being recognized be excel as spaces!! Help! | Kevin | Worksheet Functions | 4 | May 11th, 2004 12:05 AM |
| Extracting spaces from a string of numbers | johnnyrad | Worksheet Functions | 5 | February 4th, 2004 04:34 PM |
Linear Mode
