Excel deleting part of a number - URGENT- please help

[DGK101]

Hi everyone,

i have an excel spreadsheet where in column A i have numbers beginnng in 0. eg- 082 746 3833.
What i need to do is make a formula that takes the 0 out of the number. eg makes it 82 746 3833.

i would also like to know how to take the spaces out of the number- eg. 827463833.

PLEASE HELP - it is URGENT!!!
Any help would be appreciated!!
Thanks
D

 

[SkipVought]

D,

Let's make a very important distinction...

082 746 3833

is NOT a NUMBER!!!

It is a STRING!

Numbers and Strings are VERY DIFFERENT! Excel is "smart" enough that it lets you do String operaions on numbers and numeric operations on strings of digits.

Removing spaces is simple -- use Edit/Replace BTW, removing the spaces will CONVERT these strings to numbers with no leading ZEROS.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rss01]

If column A is all numbers you could use this is coulmn B


=VALUE(SUBSTITUTE(A:A," ",""))

It will take 082 746 3833 and make it 827463833 a number.

 

[DGK101]

Thanks guys!!
Worked 100%