Converting Text to Numbers

[SashaGorgov]

Hello!

I have a bunch of data that is listed vertically as text in the following format: '£3.4M' or '£287K' and sometimes '£6.9M (£7.4M)' with the brackets denoting a revised sum.

I couldn't think of a way to convert this to numbers so I can perform numerical analysis, just wondering if anyone has any ideas?

 

[JamesCanale]

MrExcelPlayground9-1.xlsx
	A	B
1	$3.4M	$ 3,400,000
2	$287K	$ 287,000
3	$6.9M ($7.4M)	$ 7,400,000
4	550 (650)	$ 650
Sheet16

Cell Formulas
Range	Formula
B1:B4	B1	=LET(z,ISNUMBER(SEARCH("(",A1)),x,IF(z,RIGHT(A1,LEN(A1)-SEARCH("(",A1)),A1),a,CODE(MID(x,SEQUENCE(LEN(x)),1)),b,FILTER(a,(a>45)*(a<58)*(a<>47)),c,VALUE(CONCAT(CHAR(b))),d,ISNUMBER(SEARCH("M",x)),e,ISNUMBER(SEARCH("K",x)),f,IF(d,c*1000000,IF(e,c*1000,c)),f)

 

[Dave Patton]

T202206a.xlsm
	A	B	C	D	E
1				K	1,000
2	£3.4M	3,400,000		m	1,000,000
3	£287K	287,000
4
3a

Cell Formulas
Range	Formula
B2:B3	B2	=LEFT(MID(A2,2,99),LEN(A2)-2)*LOOKUP(RIGHT(A2,1),$D$1:$E$2)

 

[SashaGorgov]

MrExcelPlayground9-1.xlsx
	A	B
1	$3.4M	$ 3,400,000
2	$287K	$ 287,000
3	$6.9M ($7.4M)	$ 7,400,000
4	550 (650)	$ 650
Sheet16

Cell Formulas
Range	Formula
B1:B4	B1	=LET(z,ISNUMBER(SEARCH("(",A1)),x,IF(z,RIGHT(A1,LEN(A1)-SEARCH("(",A1)),A1),a,CODE(MID(x,SEQUENCE(LEN(x)),1)),b,FILTER(a,(a>45)*(a<58)*(a<>47)),c,VALUE(CONCAT(CHAR(b))),d,ISNUMBER(SEARCH("M",x)),e,ISNUMBER(SEARCH("K",x)),f,IF(d,c*1000000,IF(e,c*1000,c)),f)

Wow, this is amazing. Thank you so much!

 

[SashaGorgov]

T202206a.xlsm
	A	B	C	D	E
1				K	1,000
2	£3.4M	3,400,000		m	1,000,000
3	£287K	287,000
4
3a

Cell Formulas
Range	Formula
B2:B3	B2	=LEFT(MID(A2,2,99),LEN(A2)-2)*LOOKUP(RIGHT(A2,1),$D$1:$E$2)

Thank you very much!!