Formula to extract price from text string

[SRpakse]

Hi All Excel Gurus,

I am trying to extract a price from a text string which is 29 characters long. The biggest price starts from the 7th character. Pretend that data is in columns A,B & C AND THE FORMULA NEEDS TO BE IN COLOUMN D.

Identifier Price String
6 $3,888,629.00 00000038886290000000C20160429

In the above there are 6 (leading) zeros before the number then it is a million + two decimal places. Right now the unique identifier is not in the data I have simply manually counted the number of zeros, therefore this might possibly have to be a formula in column A too?

Another example below;

12 $3.15 00000000000031500062C20160429

In the above there are 12 (leading) zeros before the number then it is 3 dollars + two decimal places (I.e 15cents)

The number of leading zeros so far run from 6 to 14, the data in the spreadsheet has over 350K lines so I am not really sure if a complicated excel formula is the way to go here even?

Thanks
SR

 

[wideboydixon]

Book1
	A	B	C	D
1	6	$3,888,629.00	$3,888,629.00	00000038886290000000C20160429
2	12	$3.15	$3.15	00000000000031500062C20160429
Sheet1

Cell Formulas
Range	Formula
B1	=TEXT(NUMBERVALUE(LEFT(D1, 15))/100, "$###,###,##0.00")
C1	=NUMBERVALUE(LEFT(D1, 15))/100

In Column B I've done the formatting myself. In Column C I've used the built-in currency formatting.

WBD

 

[tygrrboi]

If your original string is in A1, for example.

=DOLLAR(VALUE(REPLACE(LEFT(A1, FIND("C", A1)-1),14,0,".")),2)


This is assuming that the first 13 numbers will always be the "dollars", the next 7 will always be "cents", and that there will always be a "C" after the last digit of the price... Is this the case?