Results 1 to 4 of 4

Thread: Search for a number in a column of alpha numeric data and display only the non numeric data
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Search for a number in a column of alpha numeric data and display only the non numeric data

    Hi All,

    So I have a column which has only numbers. For eg. 345678, 674567.

    I want these individual numbers to be looked from a series of data which have corresponding Alphanumeric Values eg. 345678 - Computers, 674567 - Printers ...so on.

    How do I place a formula which looks up the individual numbers above from alpha numeric values and displays only the non numeric values i.e. the text info.

    I assume to ease the reference, the separating symbol is "-" .

    I thought of using a vlookup but wouldnt know how would that differentiate the characters to the right of the "-".

    Would appreciate all your help.

    Thank you

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Search for a number in a column of alpha numeric data and display only the non numeric data

    How about

    ABCD
    1
    2345678567890 - Computer
    3456789laptop456789 -  laptop
    4567890Computer34567 - printer
    53456

    Lookup



    Worksheet Formulas
    CellFormula
    B2=IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX($D$2:$D$4,MATCH(A2&" *",$D$2:$D$4,0)),"- ",REPT(" ",100)),100)),"")

    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jan 2019
    Posts
    57
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for a number in a column of alpha numeric data and display only the non numeric data

    Thank you for your quick response and effort.

    I tried the formula but it returns a blank value. Dont know why.

    =IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX($E$5:$E$142,MATCH(G6&" *",$E$5:$E$142,0)),"-",REPT(" ",100)),100)),"")


    Quote Originally Posted by Fluff View Post
    How about

    A B C D
    1
    2 345678 567890 - Computer
    3 456789 laptop 456789 - laptop
    4 567890 Computer 34567 - printer
    5 3456
    Lookup

    Worksheet Formulas
    Cell Formula
    B2 =IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX($D$2:$D$4,MATCH(A2&" *",$D$2:$D$4,0)),"- ",REPT(" ",100)),100)),"")

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    29,120
    Post Thanks / Like
    Mentioned
    483 Post(s)
    Tagged
    49 Thread(s)

    Default Re: Search for a number in a column of alpha numeric data and display only the non numeric data

    In that case can you please post some sample data. There are some add-ins here that enable you to do that. Add-ins
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

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
  •