excelparsingtexttrimleft-recursion

Extracting text in excel

I have some text which I receive daily that I need to seperate. I have hundreds of lines similar to the extract below:

COMMODITY PRICE DIFFERENTIAL: FEB50-FEB40 (APR): COMPANY A OFFERS 1000KB AT $0.40

I need to extract individual snippets from this text, so for each in a seperate cell, I the result needs to be the date, month, company, size, and price. In the case, the result would be:

FEB50-40
APR
COMPANY A
100
0.40

The issue I'm struggling with is uniformity. For example one line might have FEB50-FEB40, another FEB5-FEB40, or FEB50-FEB4. Another example giving me difficult is that some rows might have 'COMPANY A' and the other 'COMPANYA' (one word instead of two).

Any ideas? I've been trying combinations of the below but I'm not able to have uniform results.

=TRIM(MID(SUBSTITUTE($D7," ",REPT(" ",LEN($D7))), (5)*LEN($D7)+1,LEN($D7))) 
=MID($D7,20,21-10)
=TRIM(RIGHT(SUBSTITUTE($D6,"$",REPT("$",2)),4))

Sometimes I get

FEB40-50(' OR 'FEB40-FEB5'

when it should be

'FEB40-FEB50'`

Thank you to who is able to help.

Solution

  • You might get to the limits of formulas with this scenario, but with Power Query you can still work.

    As I see it, you want to apply the following logic to extract text from this string:

    COMMODITY PRICE DIFFERENTIAL: FEB50-FEB40 (APR): COMPANY A OFFERS 1000KB AT $0.40

    These can be easily translated into several "Split" scenarios inside Power Query.

    The screenshot shows the input data and the result in the Power Query editor after renaming the columns and before loading the query into the worksheet.

    Once you have loaded the query, you can add / remove data in the input table and simply refresh the query to get your results. No formulas, just clicking ribbon commands.

    enter image description here

    You can take this further by removing the "KB" from the column, convert it to a number, divide it by 100. Your business processing logic will drive what you want to do. Just take it one step at a time.