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

#### questforexcel

##### Board Regular
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

#### questforexcel

##### Board Regular
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)),"")

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

</tbody>
Lookup

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

</tbody>

<tbody>
</tbody>

#### Fluff

##### MrExcel MVP, Moderator
In that case can you please post some sample data. There are some add-ins here that enable you to do that. Add-ins

#### questforexcel

##### Board Regular
Hi, site skin and look of Mr. Excel is awesome! much easier to upload and put through images and sheets.

Apologies for the delayed response. Added my working here. Would really appreciate your guidance. Thank you

#### Fluff

##### MrExcel MVP, Moderator
If your columns are L & M try
=IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX(\$L\$5:\$L\$10,MATCH(M5&"*",\$L\$5:\$L\$10,0)),"- ",REPT(" ",100)),100)),"")

Also images are of very little use, there is an add-in here that enables you to post data to the site, that can be copied to and pasted. XL2BB

#### questforexcel

##### Board Regular
Thank you so much for your quick response and help.

My apologies for sharing it as an image, i downloaded the file and tried to run it but do not have access to run downloaded files.

So not able to run the Add In.

I tried using the formula and adjusted the match to M, it sill displays a blank cell.

Thank you for your time.

#### Fluff

##### MrExcel MVP, Moderator
Works for me with data like

xl2bb.xlam
LMN
5345678-PC123456
6123456-laptop345678
 Data
Cell Formulas
Range(s)Formula
N5:N6N5=IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX(\$L\$5:\$L\$10,MATCH(M5&"*",\$L\$5:\$L\$10,0)),"-",REPT(" ",100)),100)),"")

#### questforexcel

##### Board Regular
OMG!! It worked now with the latest formula above !! God Bless you thank you so much

If its okay could you please help me understand this formula above. Thank you so much

#### questforexcel

##### Board Regular
I am sorry to be more specific, what would the "*" represent in the formula after "Match" and what does REPT(" ",100)),100)),"") signify? Thank you