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

questforexcel

Board Regular
Joined
Jan 18, 2019
Messages
86
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,425
Office Version
365
Platform
Windows
How about

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;">345678</td><td style=";"></td><td style="text-align: right;;"></td><td style=";">567890 - Computer</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;">456789</td><td style=";">laptop</td><td style="text-align: right;;"></td><td style=";">456789 -  laptop</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="text-align: right;;">567890</td><td style=";">Computer</td><td style="text-align: right;;"></td><td style=";">34567 - printer</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="text-align: right;;">3456</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Lookup</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B2</th><td style="text-align:left">=IFERROR(<font color="Blue">TRIM(<font color="Red">RIGHT(<font color="Green">SUBSTITUTE(<font color="Purple">INDEX(<font color="Teal">$D$2:$D$4,MATCH(<font color="#FF00FF">A2&" *",$D$2:$D$4,0</font>)</font>),"- ",REPT(<font color="Teal">" ",100</font>)</font>),100</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br />
 

questforexcel

Board Regular
Joined
Jan 18, 2019
Messages
86
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

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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</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)),"")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,425
Office Version
365
Platform
Windows
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
Joined
Jan 18, 2019
Messages
86
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

1574108646247.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,425
Office Version
365
Platform
Windows
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
Joined
Jan 18, 2019
Messages
86
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
Joined
Jun 12, 2014
Messages
30,425
Office Version
365
Platform
Windows
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
Joined
Jan 18, 2019
Messages
86
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
Joined
Jan 18, 2019
Messages
86
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
 

Forum statistics

Threads
1,078,226
Messages
5,338,958
Members
399,272
Latest member
jakepenner

Some videos you may like

This Week's Hot Topics

Top