0

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.

Anthony
  • 123
  • 2
  • 9
  • Your question is just as unclear as your [previous one](https://stackoverflow.com/questions/66230220/extracting-text-into-a-cell-in-excel). Why 100 as size output instead of 1000? – BigBen Feb 16 '21 at 21:45
  • So if Date is always between the first colon and the first open parenthesis, then this snippet would return the date: [=MID(LEFT(A1,FIND("(",A1)-1),FIND(":",A1)+1,LEN(A1))]. Create similar or other pieces. Adjust for spaces. – Isolated Feb 16 '21 at 21:50
  • @BigBen It doesn't matter what the size is. 100 or 1000 is text not a number, I'm just interested in isolating extracting 100 in a seperate cell. – Anthony Feb 17 '21 at 07:13

1 Answers1

1

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
  • text after the first : and before the first (
  • text between the brackets
  • text after the word OFFERS and before AT
  • text after 'AT`

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

  • split by custom delimiter : - that's colon and space - for each ocurrence
  • remove first column
  • Split new first column by ( - that's space and bracket - for leftmost
  • Replace ) with nothing in second column
  • Split third column by delimiter OFFERS
  • split new fourth column by delimiter AT

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.

teylyn
  • 34,374
  • 4
  • 53
  • 73