We have been given a spreadsheet with a long list of part numbers in one of
the columns. The part numbers look like:
AR-7-IDN15-12435
There are lots of subtotal(...) equations in the spreadsheet and they all
work.

We tried to sort the data and the sort fails. The formula bar shows that
the value in the cell is
12435
and the 'AR-7-IDN12-' is there because of a custom format. In fact all the
leading characters in the column are only custom formats and not really in
the data.

We need the leading characters to summarize the data by assembly and block
numbers.

How can I convert the data so that the leading characters are in the cell
and not in the format?? I figure this must be pretty easy, but not for me.

Any help or suggestions will be greatly appreciated.
--
jake

You could try copying the column to notepad and back to Excel as this
should convert number formats to text.

Another possibility on large columns in XL2002+ is to copy and paste
with the office clipboard (by pressing ctrl+c twice and clicking the
paste icon that appears)

On Jan 30, 3:31 pm, Jakobshavn Isbrae
wrote:
We have been given a spreadsheet with a long list of part numbers in one of
the columns. The part numbers look like:
AR-7-IDN15-12435
There are lots of subtotal(...) equations in the spreadsheet and they all
work.

We tried to sort the data and the sort fails. The formula bar shows that
the value in the cell is
12435
and the 'AR-7-IDN12-' is there because of a custom format. In fact all the
leading characters in the column are only custom formats and not really in
the data.

We need the leading characters to summarize the data by assembly and block
numbers.

How can I convert the data so that the leading characters are in the cell
and not in the format?? I figure this must be pretty easy, but not for me.

Any help or suggestions will be greatly appreciated.
--
jake

Thanks. Notepad works well.
--
jake


"Lori" wrote:

You could try copying the column to notepad and back to Excel as this
should convert number formats to text.

Another possibility on large columns in XL2002+ is to copy and paste
with the office clipboard (by pressing ctrl+c twice and clicking the
paste icon that appears)

On Jan 30, 3:31 pm, Jakobshavn Isbrae
wrote:
We have been given a spreadsheet with a long list of part numbers in one of
the columns. The part numbers look like:
AR-7-IDN15-12435
There are lots of subtotal(...) equations in the spreadsheet and they all
work.

We tried to sort the data and the sort fails. The formula bar shows that
the value in the cell is
12435
and the 'AR-7-IDN12-' is there because of a custom format. In fact all the
leading characters in the column are only custom formats and not really in
the data.

We need the leading characters to summarize the data by assembly and block
numbers.

How can I convert the data so that the leading characters are in the cell
and not in the format?? I figure this must be pretty easy, but not for me.

Any help or suggestions will be greatly appreciated.
--
jake