I have a spreadsheet that has numbers and number/letter combinations that I
am trying to reformat to text so it will sort like a text sort versus a
number. I have tried refromatting all cells to text but it still does not
sort correctly. Is there a way to copy to contents of the cell to another
location and change the formatting to text?

Changing the numberformat of a cell doesn't change the underlying values.

If you're trying to convert numbers to text, how about using a helper column
with a formula like:

=a1&""
or
=text(a1,"000000")
(if you need leading zeros)

Then drag down the column and sort your data by this helper column.

Mark Gloves wrote:

I have a spreadsheet that has numbers and number/letter combinations that I
am trying to reformat to text so it will sort like a text sort versus a
number. I have tried refromatting all cells to text but it still does not
sort correctly. Is there a way to copy to contents of the cell to another
location and change the formatting to text?

--

Dave Peterson

Dave, thanks, but it didn't change any thing. Basically I'm trying to sort
about 2000 items so they start wtih "1", then "10", then "1000", "2", "20",
"2000". There are some items that have numbers and letters like 24C,
6900BXL, etc. I thought that making the numbers text would alllow it to sort
in that kind of order versus 1,2,3,4....1000, 1001, etc.

Mark

"Dave Peterson" wrote:

Changing the numberformat of a cell doesn't change the underlying values.

If you're trying to convert numbers to text, how about using a helper column
with a formula like:

=a1&""
or
=text(a1,"000000")
(if you need leading zeros)

Then drag down the column and sort your data by this helper column.

Mark Gloves wrote:

I have a spreadsheet that has numbers and number/letter combinations that I
am trying to reformat to text so it will sort like a text sort versus a
number. I have tried refromatting all cells to text but it still does not
sort correctly. Is there a way to copy to contents of the cell to another
location and change the formatting to text?

--

Dave Peterson

Nope. Changing them to text will allow them to sort as text

1
11
1234
2
23
23b
....

You're life will be lots easier if you can separate the numbers and the letters
into different columns.

Or if you do your data entry nice (all text)

00001
00001A
00011A
00023B
....



Mark Gloves wrote:

Dave, thanks, but it didn't change any thing. Basically I'm trying to sort
about 2000 items so they start wtih "1", then "10", then "1000", "2", "20",
"2000". There are some items that have numbers and letters like 24C,
6900BXL, etc. I thought that making the numbers text would alllow it to sort
in that kind of order versus 1,2,3,4....1000, 1001, etc.

Mark

"Dave Peterson" wrote:

Changing the numberformat of a cell doesn't change the underlying values.

If you're trying to convert numbers to text, how about using a helper column
with a formula like:

=a1&""
or
=text(a1,"000000")
(if you need leading zeros)

Then drag down the column and sort your data by this helper column.

Mark Gloves wrote:

I have a spreadsheet that has numbers and number/letter combinations that I
am trying to reformat to text so it will sort like a text sort versus a
number. I have tried refromatting all cells to text but it still does not
sort correctly. Is there a way to copy to contents of the cell to another
location and change the formatting to text?

--

Dave Peterson


--

Dave Peterson