Forum Discussion
Question Regarding XLookup/Numbers Stored as Text
Hoping someone can provide some insight...might be an easy answer but I haven't encountered a lot of number stored as text issues before.
Basically I'm working on a spreadsheet that has identifiers with apostrophes in front of them, from my understanding to force excel to store as text (because not all the identifies have all numbers, or any numbers). When I look at my sheet, those identifiers that have not been edited don't show a number stored as text error. When you click into the cell however and click out, the number stored as text error appears and the apostrophe disappears visually in the cell, but still shows as there in the edit bar.
Problem I'm having, any cell that's been clicked into like that - when I try to do a simple XLOOKUP function, it does not recognize the it as the same value as an unclicked cell that's still displaying the apostrophe in the visual cell. I attached a sample book with the issue I'm finding. The third set of data on the main sheet and second sheet is where the discrepancy is. Technically cell A4 on sheet 1 and A4 on sheet 2 have the same data entered, but my formula won't recognize them.
Can anyone recommend an easy way to solve this?
cguma77
Please see the attached file...
6 Replies
cguma77
Please see the attached file...- This fixed it, thank you so much!!!
cguma77 You don't really need the apostrophe, as long as you format both columns consistently See attached.
- Thanks for the answer! Few quick follow ups to that:
-Some of the identifiers do have a mix of letters, numbers, some all numbers, Do I still not need the apostrophe?
-What I'm really confused on is why excel is registering the unedited column differently than the edited one. The values in both are exactly the same. Is there a format on the cell that I'm missing?
Thanks again for the advice, really appreciate it.cguma77 Don't really understand. where does the data come from? Why are there apostrophes in the first place?
