Sort Excel Numbers Containing Assorted Numbers and Characters

**gallantca** · 11-20-2016, 04:30 PM

I have a spreadsheet with a column that has numbers that may/may not contain text and characters such as ( ). For example:

120.04-A
120.45(A)1
120.50(3)
120.55
125.21
135.36
130.65
130.65-a

When I sort the list, the numbers are at the top and those w/ text and/or special characters are at the end. How do I order the list using the first 5 numbers (###.##) and any subsequent text/characters? Thank you!

**shg** · 11-20-2016, 04:32 PM

Welcome to the board.

Select the data, format as Text, and then do Data > Text to columns, Finish.

Then sort.

**gallantca** · 11-20-2016, 04:45 PM

shg - thank you for the welcome and prompt reply. When I converted the numbers to text, I lost the number going to two places, e.g., 120.60 became 120.6. I manually added a '0' and then sorted. The data did not sort as desired, and the numbers I added the '0' to were now put with the others that are not just a number at the bottom of the list. Another sample list of what I am trying to sort:

115.00
115.01
115.05
115.08
120.03
120.04
120.00(A)(1)
120.00(A)(2)
120.00(A)(3)
130.60
130.65
260.10
263.05
135.37
130.65-a
230.08

These numbers are not properly sorted. I would like the 130.65-a to follow the 130.65.

Again, thanks for any assistance you provide.

**shg** · 11-20-2016, 07:03 PM

I pre-formatted a column to text, copied your data from post #3, and pasted as values. Here's how it sorted:

115.00
115.01
115.05
115.08
120.00(A)(1)
120.00(A)(2)
120.00(A)(3)
120.03
120.04
130.60
130.65
130.65-a
135.37
230.08
260.10
263.05

**shg** · 11-20-2016, 07:24 PM

Just to fill in some blanks:

1. Formatting numbers as Text doesn't make them text, though they do align left like text. You need to do something to convert them, and that's what text to columns does.

2. If the data is manually entered, the columns should be formatted as Text beforehand. If it's imported, it should be imported as Text.

**gallantca** · 11-20-2016, 10:02 PM

she - thanks. Yours sorted perfectly. However, for me it is still not working. I inserted a column and formatted it as text. Then copied the numbers from the original column and pasted as values. I then sorted by the new column. The numbers with a letter, hyphen, and/or parenthesis still did not sort like you were able to. I will give it another try in the morning.