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".
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".
Last edited: