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.
rayluvsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

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"
[ http://dailydoseofexcel.com/archives/2011/12/02/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"
[ http://fastexcel.wordpress.com/2011/11/30/text-vs-value-vs-value2-slow-text-and-how-to-avoid-it/ ]

BFN,

fp.

(Please don't consider this as a "solution"; just for background information to read at your leisure)
0
FlysterCommented:
You can try using the Text function. Instead of just using the reference cell, say A1, you would use =Text(A1,"#")

Flyster
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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).
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

[ fanpages ]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:

=VALUE(TEXT(A1,"#"))

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

=VALUE(TEXT(A1,"#.#"))

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.
0
FlysterCommented:
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,"#"))
0
rayluvsAuthor Commented:
Ok sounds good will be getting to my PC in a while to test it.

Thank you very much!
0
rayluvsAuthor Commented:
It seems to work, thanx!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.