3

I want to arrange my C column by numerical order, however the cells themselves have text in something like:

"503 in Kitchen & Home",

I want it to detect the number but ignore the string.

Is this possible?

2

You can extract the number to another column using REGEXEXTRACT, and then sort the range by that column. For example, suppose this is your C column:

+-----------------------+
| 503 in Kitchen & Home |
| 85.5 in Bed & Bath    |
| -263 in debt          |
| 633.26 in something   |
| 543 in Outdoors       |
+-----------------------+

Enter in column D the formula

=VALUE(REGEXEXTRACT(C1,"[0-9.-]+"))

(first row shown). Here REGEXEXTRACT extracts the first group of characters that consists only of digits, decimal dot, and minus sign. (If you don't have negative or fractional numbers, you don't need - and .) Then the VALUE command converts to numeric format.

Now you can select both columns C and D and sort the range by the D column. (Or sort the whole spreadsheet by the D column, if desired.)

+-----------------------+--------+
| -263 in debt          | -263   |
| 85.5 in Bed & Bath    | 85.5   |
| 503 in Kitchen & Home | 503    |
| 543 in Outdoors       | 543    |
| 633.26 in something   | 633.26 |
+-----------------------+--------+
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.