How to Extract A Number from String if Text within the String Equals XYZ

[tyrsfury]

Hi all, I've been scouring the net for days trying to find a valid solution for my issue. I would like to either create a macro or formula to do the following (please see attachment):

In the right side column (labeled 'Reason') there are cells with both text and numbers. Some cells contain a single word (the 'Reason') followed by the cost associated with it, some cells contain multiple words (Reasons) and costs associated with them. I need to extract the cost numbers and place their values in columns to the left. Those columns are labeled at the top with the particular 'Reason', allowing me to break out the costs and total them.

Thanks for any help in advanced.

LDG0029M-Sample.xls

[e.isayev]

This array formula extract only numbers in the beginning of the text. If there are two different numbers then it will extract first.
=MID(Y8,MATCH(1,--ISNUMBER(MID(Y8,ROW(INDIRECT("1:"&LEN(Y8))),1)+0),0),FIND(".",Y8,MATCH(1,--ISNUMBER(MID(Y8,ROW(INDIRECT("1:"&LEN(Y8))),1)+0),0)+1)-MATCH(1,--ISNUMBER(MID(Y8,ROW(INDIRECT("1:"&LEN(Y8))),1)+0),0)+3)+0

[tyrsfury]

This array formula extract only numbers in the beginning of the text. If there are two different numbers then it will extract first.
=MID(Y8,MATCH(1,--ISNUMBER(MID(Y8,ROW(INDIRECT("1:"&LEN(Y8))),1)+0),0),FIND(".",Y8,MATCH(1,--ISNUMBER(MID(Y8,ROW(INDIRECT("1:"&LEN(Y8))),1)+0),0)+1)-MATCH(1,--ISNUMBER(MID(Y8,ROW(INDIRECT("1:"&LEN(Y8))),1)+0),0)+3)+0

That's fantastic, thanks so much. There are two more steps I need to accomplish from this, I need to get the charges (number) for say "Overlength 25.00" to actually populate a "25.00" in the Overlength Column (O), same thing with 'Notify', 'COD', etc. All in their respective columns.

Second, When the Reason cell has multiple descriptions and charges, I need to break those out as well and populate the columns as detailed above. What you provided is more than half the battle and I appreciate it!

[e.isayev]

Can you write manually your desired outcomes on the file. Because reading by word seems complicated for me

[tyrsfury]

Sure, please see attached file.

I want to take the Orange Column and break out the details into the Blue Columns.

Thanks again.

[tigeravatar]

tyrsfury,

Attached is a modified version of your most recently posted workbook.
In cell O9 and copied over and down to cell W41 is this regular formula:

Please Login or Register  to view this content.

Those cells are formatted as Number with 2 decimal places.
Column X is a Sum formula, and colymn Y is =N+X

Does that work for you?

[tigeravatar]

Note that the "Beyond Jax to US Virgin" doesn't work because in your header in column V, "US" is missing

[tyrsfury]

You all are life-savers, I cannot thank you enough!

Cheers!

[e.isayev]

Hi.
Although the table was complex but I tried. The formula mostly puts the numbers but it has few exceptions.
1. Column name have to be same as in the text string otherwise it will not able to find.
2.nd. Some column names includes two separate words. In that case we have problem too. You may bring them together with "_" sign.

To make the formula work properly then you should include a words which is in the column to the text string and they have to match by 100 percent.
Have a look to the file. Check them out and you will see unadecuacies.

[tyrsfury]

Nice, that will work too! Great, thanks again.