need help extracting multi-decimal number, dollar value, and text from a cell

[namedcal]

Hello! Just wanted to say thank you in advance for any helpful posts that hopefully follow my question. I downloaded some data and there's a lot of information that appears in one cell. I would like to extract information from that cell and put them into their own cells.

Here's the data:

Cell A1:

Bought 0.0437286 units for $25.34.

Paid for with Bank of America - Bank ******0000.


Cell A2:

Bought 0.6534 units for $420.04.

Paid for with Bank of America - Bank ******2222.


Cell A3:

Bought 2.0 units for $1,372.94.

Paid for with Wells Fargo - Bank ******3333.

The columns of data I would like to extract from the each cell are the amount of units (column B), exact dollar amount (column C), and type of type of bank (column D).

For example, data extract out of A1 will be:

Cell B1: 0.0437286
Cell C1: 25.34
Cell D1: Bank of America

data extract out of A2 will be:

Cell B2: 0.6534
Cell C2: 420.04
Cell D2: Bank of America

data extract out of A2 will be:

Cell B3: 2.0
Cell C3: 1,372.94
Cell D3: Wells Fargo


For the amount of units, I am challenged due to the varying amount of units due to the after the decimal place.

For the the dollar amount, I got the following formula from another thread:

=MID(A1,FIND("$",A1),FIND(".",A1&".",FIND("$",A1))-FIND("$",A1))+0

However, this formula does not capture the amount of cents.

Finally, for the bank, I am currently doing a manual filter for the words "Bank of America" or "Wells Fargo" but I would like a formula to return the name of the bank. Perhaps something like if a cell contains "America" then return the text "Bank of America".

 

[Ron Coderre]

With your sample data in A1:A3
These regular formulas, copied down, return the values

B1: =MID(LEFT($A1,SEARCH(" units",$A1)-1),SEARCH(" ",$A1)+1,255)
C1: =SUBSTITUTE(MID(LEFT($A1,SEARCH("Paid",$A1)-1),SEARCH("$",$A1)+1,255),".","",2)
D1: =SUBSTITUTE(MID(LEFT($A1,SEARCH(" - Bank",$A1)-1),SEARCH("with",$A1)+5,255),".","",2)

Is that something you can work with?

 

[namedcal]

With your sample data in A1:A3
These regular formulas, copied down, return the values

B1: =MID(LEFT($A1,SEARCH(" units",$A1)-1),SEARCH(" ",$A1)+1,255)
C1: =SUBSTITUTE(MID(LEFT($A1,SEARCH("Paid",$A1)-1),SEARCH("$",$A1)+1,255),".","",2)
D1: =SUBSTITUTE(MID(LEFT($A1,SEARCH(" - Bank",$A1)-1),SEARCH("with",$A1)+5,255),".","",2)

Is that something you can work with?

This is exactly what I needed! Thank you so much!

 

[namedcal]

This is exactly what I needed! Thank you so much!

Just to add to the extraction formula of the dollar value: Since the formula returns a text, I added to the formula so the result is a numerical value:

=VALUE(TRIM(CLEAN(SUBSTITUTE(MID(LEFT($A1,SEARCH("Paid",$A1)-1),SEARCH("$",$A1)+1,255),".","",2))))

 

[István Hirsch]

Hope these work for you too:

B1:
=MID(A1,8,SEARCH(" ",A1,8)-8)

C1:
=-LOOKUP(1,-MID(A1,SEARCH("$",A1)+1,{1,2,3,4,5,6,7,8,9,10,11,12}))

D1:
=MID(A1,SEARCH(" with ",A1)+6,SEARCH(" - Bank",A1)-SEARCH(" with ",A1)-6)