Forum Discussion
quilkin
Feb 07, 2020Copper Contributor
xlookup not finding number/letter mix
I have a column consisting of part numbers: a mix of numbers and digit/letter combos, e.g 2, 3, 3a, 4, 5, 6, 6a, 6b, 6c, 7, 8 ...... I have formatted both the source column and the lookup column a...
Riny_van_Eekelen
Feb 08, 2020Platinum Contributor
I suspect that in one column your data was entered as numbers and text and then you formatted the columns as text. Numbers are left-justified but they are still numbers. Your other column probably consists of "real" text. Note that, when you first format the column as text and then enter data, all entries will become text. Check the nature of your data by entering =ISNUMBER(cell). TRUE means that it is a number, FALSE means that it is not. This is demonstrated in the attached file, though I used VLOOKUP as it will have the same effect.
- BCURRIE2240Feb 08, 2023Copper Contributor
Riny_van_Eekelen thanks for the explanation, which format are we trying to achieve to make it work? I use text to columns on every column, still getting the N/A when I use the formula though. When I use the =ISNUMBER(cell) I get TRUE on most columns, FALSE on one column, and don't know which one is correct.
- Riny_van_EekelenFeb 09, 2023Platinum Contributor
BCURRIE2240 Not sure that I understand how your question relates to this (rather old) thread.
Perhaps better to start a new and provide some screenshots or share a file, clarifying what you need.