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

[questforexcel]

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

 

[Fluff]

How about

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

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

 

[questforexcel]

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)),"")

How about

	A	B	C	D
	567890 - Computer
laptop	456789 - laptop
Computer	34567 - printer

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]345678[/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]456789[/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]567890[/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]3456[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Lookup​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX($D$2:$D$4,MATCH(A2&" *",$D$2:$D$4,0)),"- ",REPT(" ",100)),100)),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

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]

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]

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]

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]

Works for me with data like

Book1
	L	M	N
5	345678-PC	123456	laptop
6	123456-laptop	345678	PC
Data

Cell Formulas
Range	Formula
N5:N6	N5	=IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX($L$5:$L$10,MATCH(M5&"*",$L$5:$L$10,0)),"-",REPT(" ",100)),100)),"")

 

[questforexcel]

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]

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