+ Reply to Thread
Results 1 to 10 of 10

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

  1. #1
    Registered User
    Join Date
    11-15-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    5

    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. #2
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    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
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-15-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    5

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

    Quote Originally Posted by eisayew View Post
    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. #4
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    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. #5
    Registered User
    Join Date
    11-15-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    5

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

    Sure, please see attached file.

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

    Thanks again.
    Attached Files Attached Files

  6. #6
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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?
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  7. #7
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    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. #8
    Registered User
    Join Date
    11-15-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    5

    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. #9
    Registered User
    Join Date
    10-02-2012
    Location
    Baku
    MS-Off Ver
    Excel 2010
    Posts
    273

    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.
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    11-15-2012
    Location
    Portland, OR
    MS-Off Ver
    Excel 2010
    Posts
    5

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

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

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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