How to match a text value to a number value?

[c mateland]

Excel 2003

[lookup value = string I'm searching for]
[reference table = list I'm searching in]

I'm looping through a list of lookup values and matching them to
entries in a reference table. All I want is a true/false if the lookup
value exists in the reference table.

When performing a vlookup or match in vba, Excel sees the lookup value
as text data type but sees the reference table as a number data type,
and therefore doesn't make the match. It only has this issue when the
lookup value contains no alpha-charaters. For example, the lookup
value is 52123 and in the reference table is 52123, yet I can't get
Excel to make the match. But if the lookup value contains alpha-
characters, such as X52123, then it matches fine to X52123 in the
reference table.

) The lookup values were exported into Excel via some database
program, and the reference table was exported into Excel from some
other database program.

) All cells are formatted as text.

) I gave up on CountIf also because it couldn't tell the difference
between 052123 and 52123.

Any ideas on how I can get this right?

 

[Guest]

try converting the lookup value to a string with
Cstr(Lookup Value)

 

[c mateland]

I'd tried that and CVar, but no good. Using Type(), the lookup cell is
text type and the reference cell is number type, even though it is
formatted as text.

If I double click into the reference cells, Excel then sees them
correctly as text. But I'd have to double click over 80k cells.

try converting the lookup value to a string with
Cstr(Lookup Value)

Excel 2003

[lookup value = string I'm searching for]
[reference table = list I'm searching in]

I'm looping through a list of lookup values and matching them to
entries in a reference table. All I want is a true/false if the lookup
value exists in the reference table.

When performing a vlookup or match in vba, Excel sees the lookup value
as text data type but sees the reference table as a number data type,
and therefore doesn't make the match. It only has this issue when the
lookup value contains no alpha-charaters. For example, the lookup
value is 52123 and in the reference table is 52123, yet I can't get
Excel to make the match. But if the lookup value contains alpha-
characters, such as X52123, then it matches fine to X52123 in the
reference table.

) The lookup values were exported into Excel via some database
program, and the reference table was exported into Excel from some
other database program.

) All cells are formatted as text.

) I gave up on CountIf also because it couldn't tell the difference
between 052123 and 52123.

Any ideas on how I can get this right?- Hide quoted text -

 

[c mateland]

The ONLY solution I've found for this is to suffix an alpha character
to all values in the lookup list and reference list. Then, finally,
Excel sees it all as text (of course). Naturally, I do this in a new
column as not to muff up my original data. The MATCH function then
works using these new columns.

But still and all... is this the most professional method of dealing
with this?

 

[Guest]

Sorry - I misread your question. I thought the reference table was text and
your lookup cell was either text or numeric value. One approach you may try
is forcing your reference table to all text values using XL's TextToColumns
feature. Assuming your reference table is on sheet1, cells A1:A10 (I use the
code name for worksheets - check VBA help if you are unfamiliar with this):


Dim rngData As Range
Set rngData = Sheet1.Range("A1:A10") '<<CHANGE

rngData.TextToColumns _
Destination:=rngData.Range("A1"), _ '<<Relative reference, don't Change
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, _
Semicolon:=False, _
Comma:=False, _
Space:=False, _
Other:=False, _
FieldInfo:=Array(1, 2)


and use Cstr(lookupvalue) to ensure a text to text comparison between the
lookup value and your table. If your reference table is variable (ie - you
know it is in column A, but you don't know how many rows), you could define
rngData with something similar to:

With Sheet1
If IsEmpty(.Cells(.Rows.Count, 1)) Then
Set rngData = .Range(.Cells(1, 1), _
.Cells(.Rows.Count, 1).End(xlUp))
Else: Set rngData = .Range(.Cells(1, 1), _
.Cells(.Rows.Count, 1))
End If
End With

or you might try leaving your reference table as is, but converting your
lookup value to numeric if it can be represented as a numeric value:
If Isnumeric(lookupvalue) then
lookupvalue = Cdbl(lookupvalue)
Else: lookupvalue = Cstr(lookupvalue)
End If

then try to look the value up in the reference table. Note that if you are
using a variable for your lookup value, it will need to be declared as a
variant.

Personally, I prefer to force the data in the table to either all numeric or
all text values instead of having mixed data types just to avoid potential
confusion. Also, XL can only handle numbers to 15 digits so you have to pay
attention to format these as text when importing from text files or other
applications.

I'd tried that and CVar, but no good. Using Type(), the lookup cell is
text type and the reference cell is number type, even though it is
formatted as text.

If I double click into the reference cells, Excel then sees them
correctly as text. But I'd have to double click over 80k cells.

try converting the lookup value to a string with
Cstr(Lookup Value)

Excel 2003

[lookup value = string I'm searching for]
[reference table = list I'm searching in]

I'm looping through a list of lookup values and matching them to
entries in a reference table. All I want is a true/false if the lookup
value exists in the reference table.

When performing a vlookup or match in vba, Excel sees the lookup value
as text data type but sees the reference table as a number data type,
and therefore doesn't make the match. It only has this issue when the
lookup value contains no alpha-charaters. For example, the lookup
value is 52123 and in the reference table is 52123, yet I can't get
Excel to make the match. But if the lookup value contains alpha-
characters, such as X52123, then it matches fine to X52123 in the
reference table.

) The lookup values were exported into Excel via some database
program, and the reference table was exported into Excel from some
other database program.

) All cells are formatted as text.

) I gave up on CountIf also because it couldn't tell the difference
between 052123 and 52123.

Any ideas on how I can get this right?- Hide quoted text -