How to work with exact, without format value of a Excel 2010 cell

We would like to know how to work with the value of a cell instead of its formatted value.  For example, we have a vlookup problem that even though both the data to look for and the data in the array looks the same, both have different format.  The data in the array is in text format and the data in the lookup is numeric.  With EE we are have a solution for this situation, but the problem provoked this question.

Thus, this problem can happen in vLookup, like any other function.  Hence, how can we work with the exact value within the cell disregarding its format?  This way the functions being used can work appropriate to its purpose and design.
Who is Participating?
FlysterConnect With a Mentor Commented:
You can try using the Text function. Instead of just using the reference cell, say A1, you would use =Text(A1,"#")

[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:

As I just mentioned within another question thread, should you wish to investigate the useful (or not) nature of the .Value2 &/or .Text properties, may I suggest looking at this blog entry at Dick Kusleika's "A Daily Dose of Excel":

"A Case for Value as a Default Property"
[ ]

(You will see I comment beneath the main article text)

The article draws upon an article written by Charles Williams in his "Excel and UDF Performance Stuff" blog:

"TEXT vs VALUE vs VALUE2 – Slow TEXT and how to avoid it"
[ ]



(Please don't consider this as a "solution"; just for background information to read at your leisure)
rayluvsAuthor Commented:
Thanx for the links, we will read on it.

As for the "=text(a1,"#")", does this converts the value of the said cell to numerical? (I am not in the computer at this time).
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

[ fanpages ]Connect With a Mentor IT Services ConsultantCommented:
You're welcome.

As for the "=text(a1,"#")", does this converts the value of the said cell to numerical? (I am not in the computer at this time).

No; to change to a numerical value, you would need to enclose the result within the VALUE(...) function, thus:


However, if the value in cell [A1] has a single decimal place, then you will need to use something like:


For more decimal places, increase the number of # characters after the decimal place in the TEXT(...) function to at least the same quantity as the places you wish to include within the numeric value.

The same, or more, # characters than the quantity of decimal places is fine, but any less & the resultant value will be truncated.
If A1 is formatted as text (123) and B1 is formatted as a number (123), then A1 does not equal B1. However, A1=Text(B1,"#"). If you're using it just to find a match of 123, Text will work. If you're going to do additional math with the results, then as fanpages said you'll have to use Value(Text(B1,"#"))
rayluvsAuthor Commented:
Ok sounds good will be getting to my PC in a while to test it.

Thank you very much!
rayluvsAuthor Commented:
It seems to work, thanx!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.