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]

The "*" is a wild card meaning "anything"
The Substitute function replaces the "-" with 100 spaces, the Rept( " ",100) and then the Right function takes the last 100 characters ( the final 100 in the formula)

 

[Peter_SSs]

You may want to also consider this alternative.

Book1
	L	M	N
5	345678-PC	123456	laptop
6	123456-laptop	345678	PC
7		222222
8
Lookup

Cell Formulas
Range	Formula
N5:N8	N5	=IFNA(SUBSTITUTE(VLOOKUP(M5&"-*",L$5:L$10,1,0),M5&"-","",1),"")

 

[questforexcel]

Thank you so much. This worked too

Could you help me understand how the formula reads the text when the "substitute" formula for the "new text" reads "". Isn't "" supposed to be read as blank?

Thank you

You may want to also consider this alternative.

Book1
	L	M	N
5	345678-PC	123456	laptop
6	123456-laptop	345678	PC
7		222222
8
Lookup

Cell Formulas
Range	Formula
N5:N8	N5	=IFNA(SUBSTITUTE(VLOOKUP(M5&"-*",L$5:L$10,1,0),M5&"-","",1),"")

 

[questforexcel]

Thank you so much for this explanation and help! Much much appreciated

The "*" is a wild card meaning "anything"
The Substitute function replaces the "-" with 100 spaces, the Rept( " ",100) and then the Right function takes the last 100 characters ( the final 100 in the formula)

 

[questforexcel]

Just an edit fix requested on this. I now have come across a second set of data which has two "-" in one alpha numeric cell.

Eg.

789878 - Copiers - Colour
654698 - Copiers - Black & White

With the above formulas it is displaying "Colour" "Black & White" in results in place of "Copiers - Colour" and "Copiers - Black & White"

How Do I ensure the formula picks and removes characters before the first "-" or reads all characters after the first "-"

Thank you

 

[Fluff]

Peter's formula already does that, with mine use
=IFERROR(TRIM(RIGHT(SUBSTITUTE(INDEX($L$5:$L$10,MATCH(M5&"*",$L$5:$L$10,0)),"-",REPT(" ",100),1),100)),"")

 

[questforexcel]

Thank you for this quick response and help It worked.

I had first tried using peters formula but it read it as blanks due to extra spaces in these cell values. Your formula did read the data without the extra spaces.

However, for consistency I was trying to reduce the extra spaces in this data set but it didnt work. I used the formula below. This surprisingly worked on the 1st
data set. Thank you
=TRIM(CLEAN(SUBSTITUTE(D8,CHAR(160)," ")))

 

[Fluff]

You're welcome & thanks for the feedback

 

[questforexcel]

Surely.

Sorry one last bit on my formula above to remove the extra spaces in the alphanumeric cell. Is my formula above not correct?

=TRIM(CLEAN(SUBSTITUTE(D8,CHAR(160)," ")))

Cause it does not remove the extra spaces between the numbers and "-".

Thank you

 

[Fluff]

No that will only remove multiple contiguous spaces.