+ Reply to Thread
Results 1 to 9 of 9

Extract data for alphanumeric data

  1. #1
    Registered User
    Join Date
    11-22-2018
    Location
    hyderabad
    MS-Off Ver
    2010
    Posts
    57

    Extract data for alphanumeric data

    Hi sir and Excel expertise I like to ask you one thing I am having data with alphanumeric product data so I want to extract only alphabets form that alphanumeric
    Example

    Ab25cdf=abcdf

    Gh78v10y=ghvy

    Zxvu547tf=zxvutf

    Please help

  2. #2
    Forum Expert PaulM100's Avatar
    Join Date
    10-09-2017
    Location
    UK
    MS-Off Ver
    Office 365
    Posts
    2,108

    Re: Extract data for alphanumeric data

    Try this:
    =LEFT(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A2&{1,2,3,4,5,6,7,8,9,0}))-1)
    Click the * to say thanks.

  3. #3
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Extract data for alphanumeric data

    that cut to first number only ...

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

    Re: Extract data for alphanumeric data

    Hi,

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

    Regards
    Click * below if this answer helped

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

  5. #5
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Extract data for alphanumeric data

    that cut to first number only ...

    put into related cell ={TEXTJOIN("",TRUE,IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),""))} array so CSE needed

    I've spot now that you are using excel2010 so TEXTJOIN can't be used....
    Anyway maybe somebody else use this if needed.

    try also ={CONCAT(IF(ISERR(MID(E4,ROW(INDIRECT("1:100")),1)+0),MID(E4,ROW(INDIRECT("1:100")),1),""))}
    Last edited by KOKOSEK; 01-18-2019 at 10:26 AM. Reason: CONCAT option added

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

    Re: Extract data for alphanumeric data

    Quote Originally Posted by KOKOSEK View Post
    try also ={CONCAT("","",IF(ISERR(MID(A1,ROW(INDIRECT("1:100")),1)+0),MID(A1,ROW(INDIRECT("1:100")),1),""))}
    Isn't CONCAT also unavailable in 2010?

    Regards

  7. #7
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,737

    Re: Extract data for alphanumeric data

    Maybe... anyway as I said maybe both method will be used by someone else.
    HOWEVER yours is much quicker (no array formula).

  8. #8
    Registered User
    Join Date
    11-22-2018
    Location
    hyderabad
    MS-Off Ver
    2010
    Posts
    57
    Quote Originally Posted by PaulM100 View Post
    Try this:
    =LEFT(A2,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A2&{1,2,3,4,5,6,7,8,9,0}))-1)
    It is asked in interview

    Sir not understood
    And both i have extract in different cells like alphabets in one cell numeric in another cell

  9. #9
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Extract data for alphanumeric data

    beepetark are you open to a VBA solution?

    This User Defined Function by Rick Rothstein makes short work of this.

    If you are not familiar with how to install VBA code copy this code, go to the VBA editor (Alt F11), insert new Module and paste this into that module.

    PHP Code: 
    Function NoDigits(ByVal s As Variant) As Variant 'by Rick Rothstein
    Dim i As Long

    For i = 1 To Len(s)
    If Mid(s, i, 1) Like "#" Then Mid(s, i, 1) = Chr(1)
    Next i

    NoDigits = Replace(s, Chr(1), "")

    End Function 
    Then use this formula ... source data starting in A1
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    You will now need to save the file in a macro enabled format.
    Dave

+ 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. extract numbers from alphanumeric
    By shiva_raj in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-26-2017, 09:15 AM
  2. Extract numbers from alphanumeric
    By pradeep_as400 in forum Excel General
    Replies: 3
    Last Post: 05-18-2017, 11:13 AM
  3. [SOLVED] Extract from the last number with an alphanumeric value.
    By BrownBoy in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-06-2015, 09:12 PM
  4. extract numbers in a cell containing alphanumeric data
    By iamreese in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-09-2014, 12:38 PM
  5. [SOLVED] Extracting AlphaNumeric data from Numeric data
    By vyskcomm in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-30-2014, 06:43 PM
  6. Replies: 3
    Last Post: 04-23-2012, 10:18 AM
  7. [SOLVED] extract numeric data from alphanumeric rows using VBA / macros ?
    By h3lpMe in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 10-20-2011, 05:23 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