+ Reply to Thread
Results 1 to 10 of 10

Need to extract specific but varying length and location numbers from description

  1. #1
    Registered User
    Join Date
    06-07-2016
    Location
    MI, USA
    MS-Off Ver
    2007
    Posts
    8

    Need to extract specific but varying length and location numbers from description

    I need to find a way to retrieve, isolate, and/or extract specific numbers that are before specific words. The numbers that I need are buried in a sometimes quite lengthy description that contains both text and numbers. I need a way to just get the numbers before the words acres, A, or AC as all three are used in the descriptions which vary quite a bit. If there is a macro, add in, software, or formula that can achieve this it would save me days of work and grant you my eternal gratitude. If it helps I also have ASAP utilities installed so if there are tools on there I can use fort this that will work too. As an example of what I would like done I can only give a vague template of how the descriptions look as they are all unique. Also, I can not give an actual copy of the worksheet I need the fix for as it holds private and proprietary information so I will have to give a similar looking template example.

    AA-31 3/0 GL1 SEC 31 T56N R50W PART OF S ½ OF NE ¼ AND GL6; TH N8931'40''W 2382.34' TO THE NS ¼ LN TH S0058'02''W 2596.66' TO THE SW COR OF GL1 TH S8928'14''E 1845.80' ALG TH S LINE OF GL1 TH N4521'27''E 776.25' TH N0014'38''E 6088.75' TO THE POB. PARCEL CONTAINING 133.89 AC M/L SPLIT ON 09/27/2009 FROM 901-181-088-08; 002-131-009-00

    From this I need to get 133.89 Or 133.89 AC. Other cells have the number I want listed before the word ACRES or before just A so if there is a way that I can customize the unit after the numbers I want that would be extremely helpful as well.

    If there is any possible way to do something like this in excel then it would make my life exponentially easier. In the event that such a request is impossible if you have any ideas on how to make the task a little easier will help also. Thank you for your time and consideration on this matter in advance.

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Need to extract specific but varying length and location numbers from description

    Assuming string is in A1 enter formula in B1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Valued Forum Contributor
    Join Date
    05-08-2015
    Location
    Uvalde, TX
    MS-Off Ver
    2010
    Posts
    720

    Re: Need to extract specific but varying length and location numbers from description

    Recommend you provide a sample file with data you are working with and columns to show expected results.

    Cheers

  4. #4
    Registered User
    Join Date
    06-07-2016
    Location
    MI, USA
    MS-Off Ver
    2007
    Posts
    8

    Re: Need to extract specific but varying length and location numbers from description

    It didn't work. I received a #NA error when I entered the formula.

  5. #5
    Registered User
    Join Date
    06-07-2016
    Location
    MI, USA
    MS-Off Ver
    2007
    Posts
    8

    Re: Need to extract specific but varying length and location numbers from description

    As I said in the description I cannot give out a sample file as it has proprietary information and am bound by an NDA.

  6. #6
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Need to extract specific but varying length and location numbers from description

    Quote Originally Posted by 5bigun View Post
    It didn't work. I received a #NA error when I entered the formula.
    Don't blame formula
    Please see attached file.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-07-2016
    Location
    MI, USA
    MS-Off Ver
    2007
    Posts
    8

    Re: Need to extract specific but varying length and location numbers from description

    The formula worked for some of the descriptions but most are still giving me a #NA error or the wrong value.

  8. #8
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Need to extract specific but varying length and location numbers from description

    Quote Originally Posted by 5bigun View Post
    The formula worked for some of the descriptions but most are still giving me a #NA error or the wrong value.
    And this is fine. You only provided one example for which I wrote a formula. So, I am not sure what your expectations are.

  9. #9
    Registered User
    Join Date
    06-07-2016
    Location
    MI, USA
    MS-Off Ver
    2007
    Posts
    8

    Re: Need to extract specific but varying length and location numbers from description

    And I appreciate the help so far don't get me wrong it is just extremely difficult for me to give more precise information as each of the over 10,000 cells are unique and I legally cannot give much information about the actual contents of the file I am working on. If it helps I found an example of where the formula gave a number that wasn't present in the cell.

    A-6 1/0 ( A C) C 6 51 30 ¼ ¼ ¼ C 1 A AC C. AC CA 9 AC / 02/01/2008 001-006-003-00;

    which resulted in 0.0146666667

    Ideally if possible I would like it to result in 9.

  10. #10
    Registered User
    Join Date
    06-07-2016
    Location
    MI, USA
    MS-Off Ver
    2007
    Posts
    8

    Re: Need to extract specific but varying length and location numbers from description

    Thanks to everyone who offered their help on this issue but I ended up finding the solution I was looking for on a different forum in the end. Thank you anyways for everything and I will make sure to give you all good reviews for your assistance.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Need to extract specific but varying length and location numbers from description
    By 5bigun in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-08-2016, 10:39 AM
  2. How to extract text from cells of varying length?
    By dukefan4141 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-12-2015, 10:42 PM
  3. [SOLVED] Extract text from end of string with varying length
    By reedersketer in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-08-2015, 05:16 PM
  4. Replies: 12
    Last Post: 11-05-2012, 12:35 PM
  5. [SOLVED] Pulling specific TEXT out of a string of varying length
    By Lasloja in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-28-2012, 05:26 PM
  6. Replies: 5
    Last Post: 09-11-2012, 08:40 AM
  7. Replies: 6
    Last Post: 10-01-2009, 02:50 PM

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