+ Reply to Thread
Results 1 to 7 of 7

Formula to extract alphanumeric to a different cell

  1. #1
    Registered User
    Join Date
    06-26-2018
    Location
    USA
    MS-Off Ver
    2017
    Posts
    9

    Smile Formula to extract alphanumeric to a different cell

    Good afternoon gurus,


    I have over 2,000 of rows in a sheet. In cell A2 with a string of alphanumeric. What I would like to do is extract from cell A2 to a different cell that starts with 1X,2X,3X, and 4X (11digit long) to a different cell. Please help fix this code that can do more than one criteria.

    =MID(A2,MIN(SEARCH("1X?",A2,1),SEARCH("1X?",A2,1)),11)
    Thank you
    Attached Files Attached Files

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,343

    Re: Formula to extract alphanumeric to a different cell

    If the strings are always 52 characters (I'm going to assume the two leading spaces in A2 & A3 are not usually there) and always the same structure then why not simply
    B2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    C2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If there are leading or trailing spaces then instead of using just A2 in the formula substitute TRIM(A2)
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    06-26-2018
    Location
    USA
    MS-Off Ver
    2017
    Posts
    9

    Re: Formula to extract alphanumeric to a different cell

    Thanks, Richard, C2 works perfectly. For B2 the string are always not 52 characters long. Is there a formula that looks for a string start with 1X,2X,3x and so on?

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider (Windows 10)
    Posts
    49,795

    Re: Formula to extract alphanumeric to a different cell

    Try these:

    =TRIM(LEFT(SUBSTITUTE(MID(A2,FIND(":",A2)+2,99)," ",REPT(" ",99)),98))

    =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),98))
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  5. #5
    Registered User
    Join Date
    06-26-2018
    Location
    USA
    MS-Off Ver
    2017
    Posts
    9

    Re: Formula to extract alphanumeric to a different cell

    OMG!! Thank you both very much for your help. You guys save me tons of work by copy and paste over two thousand rows of data.

  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: Formula to extract alphanumeric to a different cell

    Try this
    Enter formula in B2 and drag formula across to C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    v A B C
    1 Material 11dgits 8digits
    2 Unrestricted-use 40 : 1XXXXXXXXXX 1628 0901 1KA12345 1XXXXXXXXXX 1KA12345
    3 Unrestricted-use 40 : 2XXXXXXXXXX 1628 0902 1KB12345
    4 Unrestricted-use 40 : 3XXXXXXXXXX 1628 0903 1KC12345
    5 Unrestricted-use 40 : 4XXXXXXXXXX 1628 0904 1KD12345
    6 Unrestricted-use 40 : 4XXXXXXXXXX 1628 0904 1KD12345
    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

  7. #7
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,343

    Re: Formula to extract alphanumeric to a different cell

    Quote Originally Posted by Confuze View Post
    Thanks, Richard, C2 works perfectly. For B2 the string are always not 52 characters long. Is there a formula that looks for a string start with 1X,2X,3x and so on?
    That's why I asked the question about the length. Your few examples were all 52 characters or 53 when a leading space was included which is why I suggested substituting TRIM(A2) for the A2 reference

+ 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. How to extract alphanumeric characters from a cell?
    By TECHO in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 12-08-2016, 01:41 AM
  2. Replies: 2
    Last Post: 05-22-2015, 02:18 PM
  3. 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
  4. Extract numbers from a alphanumeric cell
    By cpgscotcher in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-01-2013, 04:10 PM
  5. Extract Alphanumeric string from a cell
    By kenny.fsw in forum Excel General
    Replies: 1
    Last Post: 06-12-2012, 04:36 AM
  6. Extract TEXT from alphanumeric cell
    By rudeboymcc in forum Excel General
    Replies: 6
    Last Post: 10-23-2009, 01:41 PM
  7. Function to extract numbers from an alphanumeric cell
    By diana in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-13-2006, 06:00 AM

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