Forum Discussion
xlookup not finding number/letter mix
I just recently had this issue and looked high and low for a solution. What's weird is that you can define the datatype for the column to "General", but it still doesn't work for the cells that have only numbers. I was getting something like "the value is not available to the formula or function". And attempting to evaluate the formula is of no help, because it doesn't trace completely - very frustrating. So thanks a bunch for your help. BTW, I saw another post that worked as well, but it used the VALUE function to convert to "General". For some reason that function was dog slow. I saw another reply to you below about your solution slowing down a users spreadsheet as well. I'm sure it's due to the running of the function. Still weird to me that you'd have to do this conversion if you've already defined the datatype of the column - some bug within Excel maybe?
ccheatha , all depends on goals.
VALUE() converts texts which looks like numbers into numbers and returns #VALUE! error for the texts. That's okay if you try to find numbers only even if they are initially in text form.
Applying General or any other format to the column you don't change values data type. You may check result with ISNUMBER() or ISTEXT(). Formatting is only formatting, nothing else, that's by design. Values are converting from one type to another by formulae, or Paste Special operation, or conversion like Data->Text to Columns.
For example in Power Query is opposite situation. We may change data type for the column, with that default for data type format will be applied. Format in some cases could be change by formulae, e.g. if we convert date to text.
Thanks for the reply. Gotcha on the VALUE() function. Either way, I'm guessing that the application of a function will incur a performance hit; especially if the number of records involved is large.
Now, for defining the datatype of an Excel column, I'll admit to assuming that formatting a column was actually changing the datatype of the column. I think you're saying that's NOT the case, correct? If so, what actually defines the datatype of a specific Excel column?
What actually lead me to this thread was a scenario where I had some formulas based on other worksheets. I changed some of those sheets (same data columns and types, so I thought) to pull data from a database so as to refresh them when necessary. That's when the formulas stopped working and I got the error I mentioned above. The ONLY way that I was able to get it to work is with the TEXT function that you mentioned above. Yes, it actually worked with the VALUE() function, but it was dog slow. The TEXT function worked without a noticeable performance hit.
ccheatha , in Excel grid we speak about ranges, which could be one or few cells, as variant rows or columns. Type of value in the cell is defined by value itself, what we put into the cell. Format applied to such cell doesn't change value type, it only could change text representation of such value in the cell.
For such sample
Range B2:B3 is in Genera format, in B2 we have number 1 and in B3 text '1. ISNUMBER() and TYPE() show them accordingly.
Now we apply text format to B2:B3. Picture is exactly the same but B2 becomes left aligned, what is default for texts.
Convert text to number we could by formula which assumes number type as argument. In Excel auto-lifting (coercion ) rule works - if function assumes number as argument and have a string as input, it parses such string into number and tries to calculate again. If parsing works result as with number is returned, otherwise an error. Lookup functions like XMATCH work with data types as they are without coercion. In above example formatting doesn't affect the result.
If we initially applied to blank range text format, any inserted into its cell values will be considered as text.
But again, applying to B6:B7 General or number changes nothing. We may convert such strings into numbers by explicit or implicit formulae. The latest on, for example, - copy any blank not formatted cell (value for it will be considered as zero), select B6:B7, Paste->Paste Special->Add->Ok
With that we actually add to range 0, auto-lifting works, result is converting to numbers and format will be changed on General
Thanks a bunch for the explanation. I had to read through it a few times to understand it all. I'm a database developer/admin by trade, so this is a different line of thought when it comes to datatypes. I deal with structures that are predefined and the type is based on a declaration as opposed to "what you put into a column within a record" (cell/row for Excel). Anyway, your explanation helped me to understand what was going on with my scenario:
In my original workbook, where I was retrieving data from a database and performing a cut/paste (from management studio), the column of data I was comparing contained, according to Excel, some numbers and some text. When I replaced that worksheet with a data pull via PowerQuery that exact same data then ONLY contained text. I verified that with the type() function as you used in your example. That's when the problem occurred AND (via your post here) was resolved using the text() function.
Okay, I have a much better understanding of how it works now. Again, thanks so much for taking the time to explain.