Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bpeltzer
 
Posts: n/a
Default Numbers stored as text causes problem with VLOOKUP

When you've got numbers, changing the format still leaves them as numbers,
not text (so no little green corners).
You can handle the difference within your lookup formula, without having to
change the data. If your lookup_value (the first argument) is text and the
table you're looking into has numbers, you can use =vlookup(value(a1),.....).
If it's the opposite, so that the lookup_value is numeric and the table is
text (with green corners), you can use =vlookup(trim(a1),....).
To change an entire column of numbers to their text equivalents, I'd use a
helper column. If your numbers are in A1:A20, then in B1 use the formula
=trim(a1). Autofill that formula down to row 20, then copy/paste values and
delete the original data in column A.
The reverse is easier. If you've got text that you need to turn into
numbers, copy a blank cell, then highlight your data and edit paste
special, select 'add' and click ok.
HTH. --Bruce

"naclu" wrote:

I've received help here with a VLOOKUP question and it's worked great for me
so far... Thanks Max!

New question. In the current VLOOKUP that I'm doing, one of my source files
has the little green corners on the cells that tell you that numbers are
stored as text.

In the second source file, the cells are formatted to show as numbers stored
as text, but the green corners are not there.

I'm getting a lot of "no matches found" and I discovered that if I go to the
second source file, double click on the cell like I am going to edit it, hit
enter and move on, the corner is there all of a sudden and the VLOOKUP
formula works.

Is there any way to make those show up without clicking on each and every
one of them?

Thanks again in advance to anyone that might be able to help!

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Combining Text and Date for VLOOKUP biggymismyname Excel Worksheet Functions 2 December 5th 05 05:01 PM
vlookup problem, possibly due to "noise" JPANDRE Excel Worksheet Functions 2 November 16th 05 11:44 AM
Formulas dealing with text data Bagia Excel Worksheet Functions 6 June 20th 05 10:29 PM
How do I convert numbers stored as text with spaces to numbers Baffuor Excel Discussion (Misc queries) 1 May 24th 05 07:39 AM
VLOOKUP problem Wazooli Excel Discussion (Misc queries) 5 March 26th 05 02:52 PM


All times are GMT +1. The time now is 01:21 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"