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

1. ## 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 "-".

Thank you  Reply With Quote

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

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)),"")  Reply With Quote

3. ## 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)),"") Originally Posted by Fluff 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)),"")  Reply With Quote

4. ## 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  Reply With Quote

## User Tag List

data, individual, numbers, numeric, values 