# Office 365 Mac Excel 2011 numbers extracted from downloaded data sorts weird

#### sdsaav

##### New Member
Downloaded about 900 company stock market caps from Yahoo Finance the vast bulk of which is formatted like these examples:
 9.923B 5.994B 28.416B 38.361B 4.181B 8.748B 92.671B

<tbody>
</tbody>

I've tried a half-dozen solutions around the internet that have worked for others but that I can't get to work for me so I can format the entries to sort. The last was this: =IFERROR(MID(\$A2,SMALL(IF(MMULT(ABS(ISNUMBER(0+MID(MID(" "&\$A2,ROW(INDIRECT("1:"&LEN(" "&\$A2)-7)),8),{1,2,3,4,5,6,7,8},1))-{1,0,0,0,0,0,0,1}),{1;1;1;1;1;1;1;1})=8,ROW(INDIRECT("1:"&LEN(\$A2)-7))),COLUMNS(\$A:A)),6),"") [
Board Regular here]
which returned a blank cell. Others have returned #N/A or [zero].
When I try to sort my simpler solutions [eg., replace "B" with ""] I get a bunch of weird non-numerical & mixed mini-sorts like at least some entries are still read as text [no apostrophes] or something. Any suggestions? Thanks in advance! Shawn

### Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
If you want these as number only my thoughts (maybe not what you want)

B remove with substitute(A1,"B","") or similar

if its a text number then --A1 will convert to numeric (double minus)

not sureof the best way to construct the two together

1,203,205
Messages
6,054,136
Members
444,703
Latest member
pinkyar23

### 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.

### Which adblocker are you using?

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

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