Help deleted zeros only if before a non-zero

flashsam

Board Regular
Joined
Nov 13, 2003
Messages
59
Hello,

I have some cells with different variations of numbers and letters (always 4 characters though)

For example:
E357
0010
I303
T303
0150

what i need to do (if possible) in a non VBA way is:
In the cell next to where this data is i want the same information but if there are zeros at the beginning then i want the deleted from the cell.

So, for example T303 should stay the same because there is something other than a 0 in front of it
but 0150 should become 150 and 0010 should become 10

Is this possible?

Thanks in advance if anyone can answer this.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
why don't you just format the column as numbers. it would not changethe text, but your numbers would be without the leading zero's.

HTH
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,974
Office Version
  1. 365
Platform
  1. Windows
why don't you just format the column as numbers. it would not changethe text, but your numbers would be without the leading zero's.

That will change the format of the cells, but it will not change the fact that they are entered as Text values and will continue to be displayed that way.

Try this:
1. Highlight the column
2. Format the column as Numbers with 0 decimals places
3. From the Data drop down menu, select "Text to Columns"
4. Click on Finish
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
jmiskey said:
why don't you just format the column as numbers. it would not changethe text, but your numbers would be without the leading zero's.

quite right. my short comings. in addition to that you would need to convert to number (easiest w/ excels error checker in newer versions). sorry for the half-answer.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
flashsam said:
...I have some cells with different variations of numbers and letters (always 4 characters though)

For example:
E357
0010
I303
T303
0150

what i need to do (if possible) in a non VBA way is:
In the cell next to where this data is i want the same information but if there are zeros at the beginning then i want the deleted from the cell.

So, for example T303 should stay the same because there is something other than a 0 in front of it
but 0150 should become 150 and 0010 should become 10

Is this possible?...

Either

=IF(ISNUMBER(--A1),--A1,A1)

or

=IF(ISNUMBER(--A1),(--A1)&"",A1)


The first one will transform number with leading 0's into true numbers, the second into text-formatted numbers without the leading 0's.
 

flashsam

Board Regular
Joined
Nov 13, 2003
Messages
59
Thanks everyone for your input.

Aladin's method was the only one that i think i could use.

I needed to have 2 columns, one with the original data and then a formula in the second column that copied the original but without the zeros (maybe i didn't explain too well to start with!). That's why Aladin's solution was perfect because as soon as the user types the code into the first cell it copies it to the 2nd one.

(Although i couldn't get it to work until i realised i had left it saying A1. Took me a while to work that one out!)

Thanks,
 

Watch MrExcel Video

Forum statistics

Threads
1,123,334
Messages
5,601,012
Members
414,421
Latest member
tonybear1994

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