vlookup #n/a workaround

[MN_Excel]

I rrently have a spreadsheet where cell A1 contains "1234 abc". I want to isolate the first part of the value in cell B1 so I did a "left" function to get B1 to display "1234." However, I am now attempting to do a vlookup on 1234 and it keeps coming back as #n/a. I'm thinking it is due to the fact that B1 contains a formula, and not a value b/c when I do a copy/paste special values and then convert to number the vlookup works. Any ideas on a workaround that won't require me to do a paste/special values?

Thanks in advance for any help!

 

[njimack]

LEFT returns text. You need to convert to a number by one of the following:
=LEFT(A1,4)+0
=LEFT(A1,4)*1
=--LEFT(A1,4)

 

[Trouttrap2]

I think the problem is that when you did the left function it is text and your vlookup array is numeric. You'll have to convert 1234 into a number. That is my best guess right now.

You can test the cell contents with =isnumber(B1). if it comes back true its a number not text.

 

[MN_Excel]

Adding a +0 to the left function did the trick.......thanks!

 

[Erick]

I rrently have a spreadsheet where cell A1 contains "1234 abc". I want to isolate the first part of the value in cell B1 so I did a "left" function to get B1 to display "1234." However, I am now attempting to do a vlookup on 1234 and it keeps coming back as #n/a. I'm thinking it is due to the fact that B1 contains a formula, and not a value b/c when I do a copy/paste special values and then convert to number the vlookup works. Any ideas on a workaround that won't require me to do a paste/special values?

Thanks in advance for any help!

=VALUE(LEFT(A1,4)) should do the trick