+ Reply to Thread
Results 1 to 7 of 7

extract 3 letter acronym and numbers from text

  1. #1
    Registered User
    Join Date
    12-20-2012
    Location
    Des Moines, Iowa
    MS-Off Ver
    Excel 2007
    Posts
    2

    Exclamation extract 3 letter acronym and numbers from text

    Hello,

    I am trying to extract a 3 letter acronym along with numbers from text cell that could vary in length. So for example, the tla could look like one of the following

    ECU
    BTU
    NJM

    The numbers would follow this TLA with a space between. Number format could look like the following

    123.11
    1234.13
    12345.14
    123456.15

    So end result, example to extract could look like

    ECU 123456.12
    BTU 123.22

    Letters or numbers could be any combination, and could be positioned anywhere in the text. Just don't know how to get started. Thanks in advance.

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: extract 3 letter acronym and numbers from text

    Post specific examples of the full records.
    Gary's Student

  3. #3
    Registered User
    Join Date
    12-20-2012
    Location
    Des Moines, Iowa
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: extract 3 letter acronym and numbers from text

    Hello, here are 3 examples below. I highlighted in bold what I'd like to be extracted and put in another cell. thank you

    Code CCU 2613.01 (Axle lube trop basse)

    'ENGINE DRATES AND CODE ECU 2659.03 ACTIVE F OR EGR

    'Red Alert for RED CCU 002613.01 Axle L ube System - Axle Lube Pressure below acceptable level

  4. #4
    Valued Forum Contributor
    Join Date
    11-15-2008
    Location
    ph
    MS-Off Ver
    2007/2010/2016
    Posts
    479

    Re: extract 3 letter acronym and numbers from text

    Hi -

    Use TextToColumns

    Regards,
    Event

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,306

    Re: extract 3 letter acronym and numbers from text

    you provided some good examples, but could you show what your expected outcome is? it may be crystal clear to you, but not to all of us
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  6. #6
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    543

    Re: extract 3 letter acronym and numbers from text

    use array formula ie data at A1

    =MID(A1;MIN(IF(ISNUMBER(--MID(A1;ROW($1:$100);1));ROW($1:$100)))-4;COUNT(--MID(A1;ROW($1:$100);1))+5)

  7. #7
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: extract 3 letter acronym and numbers from text

    this non-array formula assumes that you will have either of CCU, ECU, BTU or NJM in a string and that the string is fewer than 255 characters long:

    Please Login or Register  to view this content.
    Last edited by icestationzbra; 12-20-2012 at 11:31 PM.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

+ 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