V-lookup formatting error

[BMR]

In row A I have about 5000 SKUs that are generally a few numbers and then a few letters.

For some reason, even though I go to "format as number", they all still have errors saying "Number Stored as Text". Because of this formatting error, the V-lookup doesn't return a value.

But as soon as I click into an individual cell to edit it, the error goes away (without changing anything, just double clicking the cell) and the v-lookup works.

I am definitely not clicking 5000 individual cells so how can I make this work?

 

[Jonmo1]

Welcome to the board..

Very common and annoying problem "numbers stored as text"


copy any blank cell
Highlight column A
Right - Click
Paste Special
Values
ADD <-very important
OK


Hope that helps.

 

[BMR]

Worked fine! What a curious problem haha. Thanks a ton for your help! I was just about to go click through them all by hitting Enter and F2 and fast as possible lol

 

[Marcol]

Try using Text to Columns

Select the column then Text to Columns > Delimited > Next > Next > Finish