+ Reply to Thread
Results 1 to 15 of 15

Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric String

  1. #1
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric String

    Dear all,

    I'm trying to extract numbers from an Alpha-Numeric String.

    I need to extract only the 1st 'n' digits from the Alpha-Numeric string where 1<n<=8.

    Input Output Scenarios
    ab1245678ab 1245678 Extracted 1st 8 digits [n=8]
    ab124cd 124 Extracted 1st 3 digits (3<=8) [n=3]
    ab12456789cd 1245678 Extracted 1st 8 digits only. Hence 9 is not present in the output [n=8]
    abcd1234acdf12 1234 Extracted 1st 4 digits (4<=8) [n=4]

    PFA for the sheet explaining the Condition and Scenarios

    I do have a formula to extract numbers but I cannot restrict it to extract upto 1st 'n' digits:
    Please Login or Register  to view this content.
    Note1: If your input is in A1 then type your formula in B1. After you have written formula, you have to Press CTRL+SHIFT+Enter

    Note2:This formula doesn't work for the last scenario mentioned above.

    Also, keep in mind that I would work with dynamic Range of data.

    I would like to achieve this through a macro if this would be efficient than writing formulas.

    Any help would be much appreciated.

    Thanks,
    Vinod Krishna
    Last edited by Vinod Krishna.C; 03-06-2014 at 01:01 PM. Reason: Added Note1 and Note2

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Try this regular formula, copied down:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Try this :

    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Quang PT

  4. #4
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Quote Originally Posted by Ron Coderre View Post
    Try this regular formula, copied down:
    Please Login or Register  to view this content.
    Is that something you can work with?
    Dear Ron,

    Your code works like a charm. Simply Brilliant

    Request you to help me understand the same.

    Regards,
    Vinod Krishna

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    @Vinod Krishna,

    you expected:

    ab12456789cd ==> 1245678
    but Ron's ==>12456789

    Does it meet your requirement?

  6. #6
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Quote Originally Posted by bebo021999 View Post
    Try this :

    Please Login or Register  to view this content.
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
    Dear Bebo,

    Your formula works brilliantly

    Also, looks simpler when compared to Ron's Solution. I hope this works even when the data grows for 10000+ rows

    Could you please explain the formula?
    Last edited by Vinod Krishna.C; 03-06-2014 at 02:42 PM.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    "ab12456789cd 1245678 Extracted 1st 8 digits only. Hence 9 is not present in the output [n=8]"

    But surely this is just a counting error?

    And where is it said that the number 9 should be categorically excluded from all strings? Saying you want the "first 8 digits" is not the same as saying that the number 9 shoud be excluded.

    Ron's solution seems perfectly in accordance with the initial description as I see it.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  8. #8
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    @Bebo021999: We each made different assumptions. I guessed that the OP wanted to limit the length of the returned value. You guessed that the OP wanted to exclude specific digits. I still don't know which of us got it right.

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Quote Originally Posted by Ron Coderre View Post
    @Bebo021999: We each made different assumptions. I guessed that the OP wanted to limit the length of the returned value. You guessed that the OP wanted to exclude specific digits. I still don't know which of us got it right.
    Surely that your formula is the best with error trap, in case limit of length of return value is 8. At first time I were confused with
    'n' digits from the Alpha-Numeric string where 1<n<=8.
    but
    ab12456789cd 1245678
    Maybe OP got typo.

    Anyway, both of us are "brilliant" , although we don't know which solution is chosen.

  10. #10
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Dear all,

    I totally forgot to include the other scenarios

    Need your help to modify your formulas for the following scenarios:

    Input: ab00000123456789cd
    Output: 00000123 [Extracted 1st 8 Digits including 0's]

    Input : ab012345678cd123
    Output: 01234567 [Extracted 1st 8 digits]

    Input : ab0cde
    Output : 0 [Extracted the digit 0]

    Logic to be applied : Irrespective ** the numbers in the alpha-numeric string, we need to extract 1st 'n digits where 1<n<=8

    Many thanks.

    Regards,
    Vinod Krishna
    Last edited by Vinod Krishna.C; 03-08-2014 at 01:35 PM.

  11. #11
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Quote Originally Posted by Ron Coderre View Post
    @Bebo021999: We each made different assumptions. I guessed that the OP wanted to limit the length of the returned value. You guessed that the OP wanted to exclude specific digits. I still don't know which of us got it right.
    Dear Ron,

    Your assumption is right

    We want to limit the length of the returned value. ( extract 1st n digits where 1<n<=8 where 8 is the no. of characters to return)

    Regards,
    Vinod Krishna

  12. #12
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Could you all please help me?

    Thanks,
    Vinod Krishna

  13. #13
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Does Ron' formula in #2 work?

  14. #14
    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: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Hi Vinod Krishna

    I think the best solution for your problem is to use VB UDF.

    Here is what you need to do:

    1. Press ALT key and F11 to open VB Editor.
    2. Go to Insert and click on Module
    3. Copy and paste code below into module and close VB Editor.

    In your spreadsheet type formula

    =LEFT(KillText(A1),8)

    A
    B
    1
    ab00000123456789cd 00000123
    2
    ab012345678cd123 01234567
    3
    ab0cde 0


    Code to use:
    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

  15. #15
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Excel Formula to Extract Numbers (atleast the 1st 8 digits) from an Alpha-Numeric Stri

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

+ 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. [SOLVED] Correctly sorting an alpha-numeric number when not all numbers have 3 digits.
    By ChristianLevi in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-09-2013, 06:47 AM
  2. Replies: 11
    Last Post: 11-16-2011, 12:56 PM
  3. Replies: 2
    Last Post: 06-18-2010, 05:10 PM
  4. Extract Numbers from Alpha-Numeric String
    By MrBill in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2005, 06:20 PM
  5. Extract Numbers from Alpha-Numeric String
    By MrBill in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM

Tags for this Thread

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