Repalce letters with actual numbers

[xeven_]

My data columns have the market cap with $1.02B and $120.35M thus not allowing us to sort properly. Is there a formula or something that can be used to convert this back into numerical data so that it can be adequately sorted, we are using excel 2016 365 proplus?

Thanks in advance

 

[JoeMo]

Assuming your entries are all either "B" or "M" in column I (below) then this will convert the text entries to millions in col J:

Excel Workbook
	I	J
1	$1.02B	1020
2	$120.35M	120.35
Sheet1

 

[shg]

Select the column and do find & replace:

B with 1E9
M with 1E6

 

[Rick Rothstein]

This formula should also work...

=IF(RIGHT(A1)="B",1000*SUBSTITUTE(A1,"B",""),0+SUBSTITUTE(A1,"M",""))

 

[xeven_]

Awesome that works thank you!!!

 

[AlKey]

Try this
Enter formula in B1 and copy down

=-LOOKUP(1,-SUBSTITUTE(A1,{"B","M"},"E"&{9,6}))

 

[shg]

Select the column and do find & replace:

B with 1E9
M with 1E6

Sorry; that should be

B with E9
M with E6

 

[AlKey]

This one will also do the job

=--SUBSTITUTE(SUBSTITUTE(A1,"B","E9"),"M","E6")