[Solved] Sorting Pot Sizes (by inches ")

[Plant Lady]

How would I sort a column that has pot diameters (1", 2", 10" etc)?

Example:

The plant size and name is imported from data in another program and it comes over in one column as below:

Example:

2" Plant
10" Plant
8" Plant

When I sort by size, it always puts the 10" plants first.

Thanks!

 

[Rafi]

Hello Plant Lady,
Thanks for your query. It seems that you want to sort data from largest to smallest number.
For doing this:
1. First remove all the characters except numbers from the cells. For example, if your cell contains the text: 5" plant, then remove the portion: " plant.
For doing this, press CTRL+H to open the Find and Replace dialog box. In the Find what field, type the characters you want to remove and keep the Replace with field blank. Then click Replace All.

This command will remove all the characters except numbers.
2. Now, select the range of cells that you want to sort > go to the Home tab > click drop-down Sort & Filter in the Editing group > select Largest to Smallest.

This command will sort your data.

Hope you find this helpful.
Regards,
Rafiul Hasan
Team ExcelDemy

 

[Plant Lady]

Rafiul,

Thanks so much for the help. I tried doing as mentioned, but it still isn't sorting properly.

I am wondering if I need to split the text to another column but haven't been able to parse out just the pot size (all numbers in front of the " sign and also the " sign to a new column.

I have attached the sheet I am working with.

I appreciate your time!

Carla

 

[Rafi]

Carla,
In your provided spreadsheet, I have noticed that there is some discontinuity in your data. The problem you are facing is because of these discontinuities. Some of the data don't contain the (") sign. So, you have to separate the data that don't have the (") sign.

1. First insert two new columns right beside the Plant Size column. Then select the column until you reach the last value > go to Data tab > click Text to Columns.

2. The Convert Text to Columns Wizard will appear. Keep the Delimited field marked > click Next.

3. In next step, mark only the Other field and type inverted comma (") here > click Next.

4. Then type a destination (I have chosen cell $C$2) where you want to show the result after splitting data > click Finish.

5. Then your numerical data and text that were connected with the (") sign will be separtaed. Now, place the data that don't have the (") sign in a separate place. I have placed them in row 466.


6. Now, select a random cell in the column that includes numerical data only > go to Home tab > click Sort & Filter under Editing group > select Largest to Smallest.


7. As a result, your data will be sorted from highest to lowest value.

8. You will find that the Header has been moved to the bottom of the sorted data.
Click on the row > press CTRL+X to cut the row.


9. Select the first row > right-click on mouse > select Insert cut cells.

10. The Header row will be pasted on the top.
That's it! Hope this time you can manage to fulfill your task.

 

[Rafi]

Let me provide you with the resolved Excel file!

Keep in touch with ExcelDemy.

Regards
Rafiul Hasan
Team ExcelDemy