formula to retrieve numbers after zero's

[moe1986]

Hi there,

Please find attached template.

I receive a file every week with thousands of lines.

The data starts of 000* and then a number.

what I need is a formula that will just give me the numbers after the zero.

I cannot change the format in column C to numbers (outside my control) so looking for a formula.

Right formula is of no use as the numbers can vary from 1 to 8 so that would mean I would have to change each formula one ata time

cheers

[pjwhitfield]

cant you just use Value()?

[zbor]

Please Login or Register  to view this content.

.

[moe1986]

Please Login or Register  to view this content.

.

I feel a bit silly now. many thanks!

[moe1986]

Just a follow up on the resolved query above.

What if the data wasn't in one column.

I.e. in the attached.

As before need a formula that will give the figure after the zero's.

There will only ever be one figure in each row

[zbor]

Doesn't matter.

Write:

Please Login or Register  to view this content.

and extend formula right and down as much as you need.

[moe1986]

When I extended the formula changes to "=--B5:G5" but this then gives me #Value! error message? am I missing something?

[zbor]

You missing everything :D

You need to extend this formula from first cell to the right.
Formula will change reference as you move it to the right.
When you come to the =--G5 you can drag formula down to get all rows.

Like this (only imagine your formula in it): http://www.excel-2010.com/wp-content...ther-cells.gif

[moe1986]

ah I see where the confusion is. I actually attached a second file in my reply at 12.37p.m

It was a follow up on the original file but in a different format.

Rather then the data being in one column they are now spread over say 5 different columns. There is a one figure per row but in different columns. So looking for a similar formula but one that will account for the data in different columns.