Extracting data from a text string (XL 2003)

Topic

New Reply

I have the following text in a column “** CLAIMS ** YR2006 NO. 1 INC $1,885.46: YR2008 NO. 2 INC $11,750.00”

What I want to do is separate out the no’s inc and add them together in another column and then to separate out all the $ values in another column so that I end up with Claim No’s Inc and Claim $

Does anyone know a formula that can do this easily?

Regards,
Maria

Reply | Quote

Replies

Can you upload a sample file with a before and after examples?

Reply | Quote

Can you upload a sample file with a before and after examples?

Here is a sample extracted from the original file, showing original text data and desired output into 2 columns.

Reply | Quote

I’ve added 2 UDFs to your file and used them in two additional columns (so you can check them against your figures!)

Reply | Quote

I’ve added 2 UDFs to your file and used them in two additional columns (so you can check them against your figures!)

Rory,

Thanks heaps – your UDFs will save me heaps of time as the spreadsheet in question had over 20,000 rows of data that I had to extract these figures from.

Regards,
Maria

Reply | Quote