I want to create a formular in a new numeric cell that removes spaces from
another cell ie 0800 123 456 result 0800123456

Hi
Use this function
B1: =SUBSTITUTE(A1," ","")
A1: 0800 123 456

"Joco" wrote:

I want to create a formular in a new numeric cell that removes spaces from
another cell ie 0800 123 456 result 0800123456

Joco wrote:
I want to create a formular in a new numeric cell that removes spaces from
another cell ie 0800 123 456 result 0800123456

Hi Joco,

Maybe you can use something like this if your phonenumber is in A1

=SUBSTITUTE(A1," ","")

Regards,
Bondi

You dont need a formula. Just use Ctrl-F and select Replace.
Enter a space in Find what field and leave the Replace with field
blank.

You will have to reformat the cells to text as when Excel removes the
spaces it will interpret the result as a number


--
Special-K


------------------------------------------------------------------------
Special-K's Profile: http://www.excelforum.com/member.php...fo&userid=7470
View this thread: http://www.excelforum.com/showthread...hreadid=563198

If the cell wit the text in was A3

=SUBSTITUTE(A3," ","") would loose the spaces, but it would still be
formated as text

=SUBSTITUTE(A3," ","")*1 would turn it into a number, but you would
loose leading 0 on phonenumbers. If you formatted to custom and
0000000000

it would produce a leading 0 but if other phonenumbers wee different
lengths it would fail, it is better staying as text

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=563198

Dav Thanks

I have used your formula, and formatted the column with the formulain to
0000's equal to the longest phone number, and formatted the cell I use for
the enquiery the same way.

The column with the formula is hidden so the addition 00's on the front does
not matter, end result is great

thanks

"Dav" wrote:


If the cell wit the text in was A3

=SUBSTITUTE(A3," ","") would loose the spaces, but it would still be
formated as text

=SUBSTITUTE(A3," ","")*1 would turn it into a number, but you would
loose leading 0 on phonenumbers. If you formatted to custom and
0000000000

it would produce a leading 0 but if other phonenumbers wee different
lengths it would fail, it is better staying as text

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=563198

Thanks for your formula, you need to add *1 to the end of it to return the
field to a number field

Without you help I would still be strugling

Thanks

"Muhammed Rafeek M" wrote:

Hi
Use this function
B1: =SUBSTITUTE(A1," ","")
A1: 0800 123 456

"Joco" wrote:

I want to create a formular in a new numeric cell that removes spaces from
another cell ie 0800 123 456 result 0800123456

Thanks for your formula, however I have been advised you need to add *1 to
the end in order to return the cell to a numeric one

thanks for your help.

"Bondi" wrote:


Joco wrote:
I want to create a formular in a new numeric cell that removes spaces from
another cell ie 0800 123 456 result 0800123456

Hi Joco,

Maybe you can use something like this if your phonenumber is in A1

=SUBSTITUTE(A1," ","")

Regards,
Bondi