Excel: Separate Currency Values from Rest of Line

Question

I have the following conundrum, which I think will be helpful for a lot of people. I have been pulling down data from bank statements (yes, I could use Mint; no, I don't want to for various reasons), and I have ended up with results such as the following (slight redaction for privacy, # in place of numbers):

SUNOCO ########## SOMEPLACE CA #### ##### 23.16

The column used to have two dates in front of the transaction, but I used Text-To-Columns to break them out. The problem now, however, is that I need to break out the dollar value (23.16) out of the rest of the line, but cannot use fixed width (the values are longer or shorter) and cannot delimit by a character (because I would have to manually insert something like a semi-colon into every single line).

Perhaps a find/replace? There are no other decimals in the lines except for the money values, so optimally, that would replace anything conforming to a mask with a semicolon + the value (e.g. 1,231 would become ; 1,231) and then I could use Text-To-Columns to separate based on the semicolon. But I am not sure how to do that.

Thoughts? I really don't want to break all values out manually.

Answer 1

Using Excel 2016 you have several options.

1. Flash Fill - Type the first number into the next empty column. Start typing the second number and see how Flash Fill suggests the rest. Accept the suggestion with the Enter key.

2. Get and transform - Make the data into a table (Insert > Table), then click Data > Get & Transform > From Table. Click the column with the data, then the Transform ribbon > Split columns > by delimiter > select the space and tick "At the right-most delimiter". Hit OK and rename the columns, then save the query and load it to the workbook.

You can now add more data to your original data table and refresh the query to perform the same operation again.