a formular to remove spaces in a numeric cell (Phone No)

J

Joco

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

Bondi

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

Hi Joco,

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

=SUBSTITUTE(A1," ","")

Regards,
Bondi
 
S

Special-K

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

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

Dav

If the cell wit the text in was A3

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

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

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

Regards

Da
 
J

Joco

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
 
J

Joco

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
 
J

Joco

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.
 
Top