Results 1 to 7 of 7

Extract Text from Alpha Numeric String on Excel without use of VBA

This is a discussion on Extract Text from Alpha Numeric String on Excel without use of VBA within the Excel Questions forums, part of the Question Forums category; HI, I want to extract TEXT from the begining of the the below mention Alphanumeric string with the help of ...

  1. #1
    New Member
    Join Date
    Jul 2012
    Posts
    11

    Default Extract Text from Alpha Numeric String on Excel without use of VBA

    HI,

    I want to extract TEXT from the begining of the the below mention Alphanumeric string with the help of formulae and not VBA. Please help:

    ROW A
    ALR300M13
    AMZN1600V12

    from the above string I want my output as the below:

    ALR
    AMZN in Row B from ROW A

  2. #2
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: Extract Text from Alpha Numeric String on Excel without use of VBA

    Hi
    Try:

    Excel 2007
    AB
    1ALR300M13ALR
    2AMZN1600V12AMZN

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890))-1)

    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

  3. #3
    Board Regular
    Join Date
    Oct 2003
    Posts
    173

    Default Re: Extract Text from Alpha Numeric String on Excel without use of VBA

    Code:
    =left(a14,find("0",substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(substitute(a14,"1","0",1),"2","0",1),"3","0",1),"4","0",1),"5","0",1),"6","0",1),"7","0",1),"8","0",1),"9","0",1))-1)

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,332

    Default Re: Extract Text from Alpha Numeric String on Excel without use of VBA

    Welcome to the board.

    Try
    =LEFT(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&{1,2,3,4,5,6,7,8,9,0}))-1)
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    New Member
    Join Date
    Jul 2012
    Posts
    11

    Default Re: Extract Text from Alpha Numeric String on Excel without use of VBA

    Thanks it work perfectly for me...

  6. #6
    New Member
    Join Date
    Jul 2012
    Posts
    11

    Default Re: Extract Text from Alpha Numeric String on Excel without use of VBA

    I have a question agin for the above query but if my Alpha Numeric String is like below how do I Extract the TEXT before . from the same list of string

    Column A
    FB350H12
    FNP140G12
    FNP150G12
    PCLN6400S12
    PCX.US10X12
    PCX.US15X12

    Output I want is:
    FB
    FNP
    FNP
    PCLN
    PCX
    PCX

  7. #7
    Board Regular
    Join Date
    Dec 2011
    Posts
    3,638

    Default Re: Extract Text from Alpha Numeric String on Excel without use of VBA

    You could use this variant:

    Excel 2007
    AB
    1ALR300M13ALR
    2AMZN1600V12AMZN
    3PCX.US15X12PCX

    Sheet1



    Worksheet Formulas
    CellFormula
    B1=LEFT(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0,"."},A1&123456780.9))-1)

    For testing your Regular Expression patterns, try my Regular Expression Pattern Testing add-in!

    For notes on how to use it - see here.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com