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

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

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

Originally Posted by eisayew

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!

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

Sure, please see attached file.

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

Thanks again.

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

Cheers!

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.

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