If I have a list:

1000000
3000000
10000000
100000000

Can I formulaically extract:
1
3
10
100
(I'd like to do this without performing any mathematical operations on the
data as the output are being fit into a concatenate formula).
Thanks.

=left(value,len(value)-6)

"Singh" wrote:

If I have a list:

1000000
3000000
10000000
100000000

Can I formulaically extract:
1
3
10
100
(I'd like to do this without performing any mathematical operations on the
data as the output are being fit into a concatenate formula).
Thanks.

What will you be concatenating?

The original values or the new values?

Without using any formulas or helper cells.

Enter 1000000 in an unused cell.

Copy that cell then select the range to change and EditPaste
SpecialDivideOKEsc

Delete the cell with 1000000


Gord Dibben MS Excel MVP


On Mon, 22 Sep 2008 11:51:01 -0700, Singh
wrote:

If I have a list:

1000000
3000000
10000000
100000000

Can I formulaically extract:
1
3
10
100
(I'd like to do this without performing any mathematical operations on the
data as the output are being fit into a concatenate formula).
Thanks.

I don't know if this will be acceptable for what you are trying to do or
not, but if you Custom Format the cells using this... 0,, (a zero followed
by 2 commas), then your values will display everything except the last 6
digits (if those 6 digits are not all zero as you show, then they will be
rounded up or down as appropriate).

--
Rick (MVP - Excel)


"Singh" wrote in message
...
If I have a list:

1000000
3000000
10000000
100000000

Can I formulaically extract:
1
3
10
100
(I'd like to do this without performing any mathematical operations on the
data as the output are being fit into a concatenate formula).
Thanks.