easy formating question

[DanielG]

Hi folks, here's an easy one:

I have a two columns of data that I need to format the same way in order to make a formula work:

A
00300127877
00300034277
00300194885
00300201116

B
00300127877
00300192264
00300016779
00300178541

My question is: How can I eliminate the two zero's in front of all these numbers??

Thanks alot!

 

[yee388]

Hey Daniel. Have you tried changing the number format to "General"?

 

[Andrew Poulsom]

Is your problem that one set is numbers and the other set is text? You can convert from one to the other with Data|Text To Columns, specifying the Column data format at Step 3.

 

[DanielG]

Well, I tried making both columns General, both columns Text, etc... and my formula is only working for those few numbers in one of my columns that don't have zeros in front. So basically, I just need to know how to format cells to eliminate a specified number of characters from the data string.

Thanks again..

 

[yee388]

=+RIGHT(A1,LEN(A1-2))

should 1) take all but the first 2 characters of a string and 2) convert it to a number that can be used in a formula.

However, if you format the cells as a number, there will never be leading zeros in it, unless you go out of your way to do so. You might try copy -> paste special: values into a range that's already formatted as a number.