LinkBack URL
About LinkBacks
I've got a list of numbers where the B's and M's represent millions and billions -
would someone be able to tell me how to sort them properly in Excel please?
sample:
$10.7B
$10.82B
$10.84B
$10.87M
$10.89B
$10.8B
$10.94B
$10.96M
I've got a list of numbers where the B's and M's represent millions and billions -
would someone be able to tell me how to sort them properly in Excel please?
sample:
$10.7B
$10.82B
$10.84B
$10.87M
$10.89B
$10.8B
$10.94B
$10.96M
Because they are not real numbers, but text, excel will sort them as text (which Im sure you have found).
Try extracting the last digit to a helper column, then sort based on that
=right(a1,1)
copied down
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Replace M with e5 and B with e6 to covert them to numbers. Then sort.
One test is worth a thousand opinions.
Click the * Add Reputation below to say thanks.
Thanks for that FD! Now I have a new problem, Excel is not sorting based on the
decimal point - should I be removing the '$' symbol somehow perhaps?
sample:
$925.73M
$93.23M
$939M
$94.31M
$941.72M
Or formula for helper column:
=LEFT(A1,LEN(A1)-1)*LOOKUP(RIGHT(A1,1),{"B","M"},{1000000000,1000000})
Thanks guys, but I think Marvin had the quickest solution so far - only replace M with E6 and B with E9
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks