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.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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,
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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