easy formating question

DanielG

New Member
Joined
Oct 9, 2006
Messages
4
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

yee388

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
Hey Daniel. Have you tried changing the number format to "General"?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Oct 9, 2006
Messages
4
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

Well-known Member
Joined
Mar 7, 2004
Messages
1,374
=+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.
 

Forum statistics

Threads
1,141,059
Messages
5,704,034
Members
421,323
Latest member
Exidous

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top