cells formatting issue

D

Davidelan

Hi everybody,

unfortunately I don't know Excel very well that's why you see this
message. Anyway I have a column of data with text of the type:

01000-001
01000-002
01000-003
....
02000-001
....

what I need to do is get rid of the 0 in front of each number (which is
formatted as text at the moment, I think).
So the result should be:

1000-001
1000-002
1000-003
....
2000-001
....

I need to convert thousands of these cells so I can't really do it manually.

I apologize if this is a silly problem, but please help me if you can.

Thanks a lot.

Davide.
 
T

tjtjjtjt

If you don't mind the numbers remaining as text, create a blank column (often
referred to as a Helper Column) and type the following formula as one
possible solution:
=RIGHT(F13,LEN(F13)-1)
where the text to change is in cell F13

tj
 
D

Davidelan

tjtjjtjt said:
If you don't mind the numbers remaining as text, create a blank column (often
referred to as a Helper Column) and type the following formula as one
possible solution:
=RIGHT(F13,LEN(F13)-1)
where the text to change is in cell F13

tj
Click to expand...

Hi tj,

forgive me for asking this I'm sure it is obvious, but how do I apply
that to all rows? Basically how do I change automatically
=RIGHT(F13,LEN(F13)-1) (which works perfectly) to:


=RIGHT(F14,LEN(F14)-1)
=RIGHT(F15,LEN(F15)-1)
=RIGHT(F16,LEN(F16)-1)
....
....

and so on?

Thank you very much!!

Davide.
 
F

Frank Kabel

Hi
just copy the formula down. Excel will change the row reference
automatically
 
G

Gord Dibben

To copy down........

Hover mouse pointer over bottom right corner of the cell with the formula.

If you have Tools>Options>Edit "allow cell drag and drop" enabled you will see
a black cross appear.

Click on that and drag down the column. As Frank says, the cell references
will increment as you drag.

Gord Dibben Excel MVP
 
Top