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

1. ## 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

2. ## Re: How to Extract A Number from String if Text within the String Equals XYZ

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

3. ## Re: How to Extract A Number from String if Text within the String Equals XYZ

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!

4. ## Re: How to Extract A Number from String if Text within the String Equals XYZ

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

5. ## Re: How to Extract A Number from String if Text within the String Equals XYZ

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

Thanks again.

6. ## Re: How to Extract A Number from String if Text within the String Equals XYZ

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?

7. ## Re: How to Extract A Number from String if Text within the String Equals XYZ

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

8. ## Re: How to Extract A Number from String if Text within the String Equals XYZ

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

Cheers!

9. ## Re: How to Extract A Number from String if Text within the String Equals XYZ

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.

10. ## Re: How to Extract A Number from String if Text within the String Equals XYZ

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1