special sort on telephone numbers

Yaseraliakram

New Member
Joined
Nov 14, 2019
Messages
14
hi guys i have a table below.
In example a if i do the simple sort function i get result A, But i need to get to result B can anyone help me with this ?



Result A Result B
Algeria Mobile2135Algeria Mobile2135
Algeria Mobile2136Algeria Mobile21355
Algeria Mobile21355Algeria Mobile21356
Algeria Mobile21356Algeria Mobile213560
Algeria Mobile21365Algeria Mobile213561
Algeria Mobile21366Algeria Mobile2136
Algeria Mobile21367Algeria Mobile21365
Algeria Mobile21368Algeria Mobile213659
Algeria Mobile21369Algeria Mobile21366
Algeria Mobile213560Algeria Mobile21367
Algeria Mobile213561Algeria Mobile213670
Algeria Mobile213659Algeria Mobile21368
Algeria Mobile213670Algeria Mobile21369

<colgroup><col style="mso-width-source:userset;mso-width-alt:3140;width:69pt" width="92"> <col style="width:51pt" width="68" span="2"> <col style="mso-width-source:userset;mso-width-alt:3140;width:69pt" width="92"> <col style="width:51pt" width="68"> </colgroup><tbody>
</tbody>
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
It appears to be sorting based on values. If you make another column with a formula like = text(value('phone number reference cell',"general")) and sort on that you should get what you want
 
Upvote 0
See if this does what you want. Add the following formula to a third column & copy down.


Excel 2016
ABC
2Algeria Mobile2135Algeria Mobile2135
3Algeria Mobile2136Algeria Mobile2136
4Algeria Mobile21355Algeria Mobile21355
5Algeria Mobile21356Algeria Mobile21356
6Algeria Mobile21365Algeria Mobile21365
7Algeria Mobile21366Algeria Mobile21366
8Algeria Mobile21367Algeria Mobile21367
9Algeria Mobile21368Algeria Mobile21368
10Algeria Mobile21369Algeria Mobile21369
11Algeria Mobile213560Algeria Mobile213560
12Algeria Mobile213561Algeria Mobile213561
13Algeria Mobile213659Algeria Mobile213659
14Algeria Mobile213670Algeria Mobile213670
Sort
Cell Formulas
RangeFormula
C2=A2&B2



Then select all 3 columns and sort on the new formula column. The result as follows and you can delete the extra column if desired


Excel 2016
ABC
2Algeria Mobile2135Algeria Mobile2135
3Algeria Mobile21355Algeria Mobile21355
4Algeria Mobile21356Algeria Mobile21356
5Algeria Mobile213560Algeria Mobile213560
6Algeria Mobile213561Algeria Mobile213561
7Algeria Mobile2136Algeria Mobile2136
8Algeria Mobile21365Algeria Mobile21365
9Algeria Mobile213659Algeria Mobile213659
10Algeria Mobile21366Algeria Mobile21366
11Algeria Mobile21367Algeria Mobile21367
12Algeria Mobile213670Algeria Mobile213670
13Algeria Mobile21368Algeria Mobile21368
14Algeria Mobile21369Algeria Mobile21369
Sort
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top