Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
Hi there,
Could anyone please advise how to remove 'spaces' that are contained within a number string in a cell? e.g. 123 4545 4545 Please advise how to remove the spaces in between, with any function/formula to obtain the desired result of '12345454545' i.e. by deleting the spaces. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
=SUBSTITUTE(A1," ","")
If this post helps click Yes --------------- Jacob Skaria "Munfarid" wrote: Hi there, Could anyone please advise how to remove 'spaces' that are contained within a number string in a cell? e.g. 123 4545 4545 Please advise how to remove the spaces in between, with any function/formula to obtain the desired result of '12345454545' i.e. by deleting the spaces. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
OR
A1 = 123 4545 4545 In B1 =SUBSTITUTE(A1,CHAR(32),) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: =SUBSTITUTE(A1," ","") If this post helps click Yes --------------- Jacob Skaria "Munfarid" wrote: Hi there, Could anyone please advise how to remove 'spaces' that are contained within a number string in a cell? e.g. 123 4545 4545 Please advise how to remove the spaces in between, with any function/formula to obtain the desired result of '12345454545' i.e. by deleting the spaces. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
Thanks alot Dear,
What, a) if the space(es) is/are at the start of the number string e.g " 123456" b) if the space(es) is/are at the end of the number string e.g "123456 " Please advise on this as well. "Munfarid" wrote: Hi there, Could anyone please advise how to remove 'spaces' that are contained within a number string in a cell? e.g. 123 4545 4545 Please advise how to remove the spaces in between, with any function/formula to obtain the desired result of '12345454545' i.e. by deleting the spaces. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
Substitute will take off all spaces including starting.. If you want to keep
the spaces inbetween use the TRIM() function. A1 = " 12345" B1 = Trim(A1) If this post helps click Yes --------------- Jacob Skaria "Munfarid" wrote: Thanks alot Dear, What, a) if the space(es) is/are at the start of the number string e.g " 123456" b) if the space(es) is/are at the end of the number string e.g "123456 " Please advise on this as well. "Munfarid" wrote: Hi there, Could anyone please advise how to remove 'spaces' that are contained within a number string in a cell? e.g. 123 4545 4545 Please advise how to remove the spaces in between, with any function/formula to obtain the desired result of '12345454545' i.e. by deleting the spaces. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
I have tried this, but its working in some instances and is not in others..
apparently, the number string contains spaces, and since the formula isnt working, i guess that these are not 'spaces' at all, rather something elsen'invisible' please advise "Jacob Skaria" wrote: Substitute will take off all spaces including starting.. If you want to keep the spaces inbetween use the TRIM() function. A1 = " 12345" B1 = Trim(A1) If this post helps click Yes --------------- Jacob Skaria "Munfarid" wrote: Thanks alot Dear, What, a) if the space(es) is/are at the start of the number string e.g " 123456" b) if the space(es) is/are at the end of the number string e.g "123456 " Please advise on this as well. "Munfarid" wrote: Hi there, Could anyone please advise how to remove 'spaces' that are contained within a number string in a cell? e.g. 123 4545 4545 Please advise how to remove the spaces in between, with any function/formula to obtain the desired result of '12345454545' i.e. by deleting the spaces. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
Dear Munfarid
4 possibilities...... 1. Check the formula bar to see whether the space is displayed.. If it is not; then you might have a custom format for the cell...IF so check the cell format 2. If it is a line break you can use the below to replace those... =SUBSTITUTE(A1,CHAR(10),) 3. OR to identify the character using the below function.. Suppose you have an entry A1 = " 12345" Use the below function to get the character code =CODE(MID(A1,1,1)) If the space is in between like (4th position) A1 = "123 45" =CODE(MID(A1,4,1)) Once you get the character code then you can replace that code using the SUBSTiTUTE function 4. A wild guess. I hope it is not an indent...If so select cell and decrease indent level If this post helps click Yes --------------- Jacob Skaria "Munfarid" wrote: I have tried this, but its working in some instances and is not in others.. apparently, the number string contains spaces, and since the formula isnt working, i guess that these are not 'spaces' at all, rather something elsen'invisible' please advise "Jacob Skaria" wrote: Substitute will take off all spaces including starting.. If you want to keep the spaces inbetween use the TRIM() function. A1 = " 12345" B1 = Trim(A1) If this post helps click Yes --------------- Jacob Skaria "Munfarid" wrote: Thanks alot Dear, What, a) if the space(es) is/are at the start of the number string e.g " 123456" b) if the space(es) is/are at the end of the number string e.g "123456 " Please advise on this as well. "Munfarid" wrote: Hi there, Could anyone please advise how to remove 'spaces' that are contained within a number string in a cell? e.g. 123 4545 4545 Please advise how to remove the spaces in between, with any function/formula to obtain the desired result of '12345454545' i.e. by deleting the spaces. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
May be the following with help you to understand the issue:
The cell A1 contain "4353495894385 ". When i use the formula =LEN(A1), the result is "18" but when i use the formula =SUBSTITUTE(A115," "," "), the result is also comes at '18'. The result should rather be '13'. This indicates that the later portion of the numberstring is appeared to be 'spaces' which the substitute formula is not recognising. I hope you got my point. If not, please advise. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
On Tue, 26 May 2009 04:16:01 -0700, Munfarid
wrote: I have tried this, but its working in some instances and is not in others.. apparently, the number string contains spaces, and since the formula isnt working, i guess that these are not 'spaces' at all, rather something elsen'invisible' If you have downloaded this data from the web, or an html document, some of the <space's may really be <nbsp's. So try =substitute(SUBSTITUTE(A1," ",""),char(160),"") --ron |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
I have tried to identify the character using
=CODE(MID(A1,1,1)) and the result i got is "52" now wat does this '52' mean? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
Your formula is replacing spaces with spaces
=SUBSTITUTE(A115," "," ") Try changing that to..the below (Note the different between the find string (" ") and replace string ("") =SUBSTITUTE(A115," ","") OR =SUBSTITUTE(A115,CHAR(32),) If this post helps click Yes --------------- Jacob Skaria "Munfarid" wrote: May be the following with help you to understand the issue: The cell A1 contain "4353495894385 ". When i use the formula =LEN(A1), the result is "18" but when i use the formula =SUBSTITUTE(A115," "," "), the result is also comes at '18'. The result should rather be '13'. This indicates that the later portion of the numberstring is appeared to be 'spaces' which the substitute formula is not recognising. I hope you got my point. If not, please advise. |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
opps. please
May be the following will help you to understand the issue: The cell A1 contain "4353495894385 ". When i use the formula =LEN(A1), the result is "18" but when i use the formula =SUBSTITUTE(A1," ",""), the result is also comes at '18'. The result should rather be '13'. This indicates that the later portion of the numberstring is appeared to be 'spaces' which the substitute formula is not recognising. I hope you got my point. If not, please advise. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
BINGOO!!!!
Ron Rosenfeld THANKS INDEED!! ALL PRAISE TO JACOB AS WELL... |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
With "4353495894385 " in A1 try
=CODE(RIGHT(A1,1)) and say your code is xx =SUBSTITUTE(A1,CHAR(xx),) If this post helps click Yes --------------- Jacob Skaria "Munfarid" wrote: I have tried to identify the character using =CODE(MID(A1,1,1)) and the result i got is "52" now wat does this '52' mean? |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
52 means that the first character of your string is 4.
I guess that you probably intended to look at a character other than the first, so to look at the nth character, change =CODE(MID(A1,1,1)) to =CODE(MID(A1,n,1)) -- David Biddulph "Munfarid" wrote in message ... I have tried to identify the character using =CODE(MID(A1,1,1)) and the result i got is "52" now wat does this '52' mean? |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to remove space from a cell.
On Tue, 26 May 2009 04:46:01 -0700, Munfarid
wrote: BINGOO!!!! Ron Rosenfeld THANKS INDEED!! You're welcome. Glad to help. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove space in front of number in a cell? | Excel Worksheet Functions | |||
Macro to remove space at front and end of a cell | Excel Discussion (Misc queries) | |||
How to remove a space | Excel Discussion (Misc queries) | |||
How do I remove a space infront of text in a cell? | Excel Discussion (Misc queries) | |||
Function to remove a space from text in cell WITHOUT macro?? | Excel Worksheet Functions |