How to remove space from a cell.

[Munfarid]

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

 

[Jacob Skaria]

=SUBSTITUTE(A1," ","")

If this post helps click Yes

 

[Jacob Skaria]

OR
A1 = 123 4545 4545

In B1
=SUBSTITUTE(A1,CHAR(32),)

If this post helps click Yes

 

[Munfarid]

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.

 

[Jacob Skaria]

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

 

[Munfarid]

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]

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

 

[Munfarid]

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.

 

[Ron Rosenfeld]

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

 

[Jacob Skaria]

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

 

[Munfarid]

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?

 

[Munfarid]

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.

 

[Munfarid]

BINGOO!!!!

Ron Rosenfeld THANKS INDEED!!

ALL PRAISE TO JACOB AS WELL...

 

[Jacob Skaria]

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

 

[David Biddulph]

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

 

[Ron Rosenfeld]

BINGOO!!!!

Ron Rosenfeld THANKS INDEED!!

You're welcome. Glad to help. Thanks for the feedback.
--ron