An excel file that was exported from an Access database was supplied to me to import into a database. One spreadsheet has a column called type class that works OK (sheet 1), but when I moved it to sheet 2 to use VLOOKUP, I can't immediately identify if it's a text column or a number column. Not all cells are displaying the green object in the upper left. nonetheless, the ISTEXT function produces text. The original column in its original form, together with the ISTEXT outcome, are shown below.
when I use the column in a VLOOKUB function to transfer the Name to the first sheet, only (1010, 1101, 1102,....), hence the cells with the green mark on the upper-left corner.
The key in sheet 1 can be easily formatted using text-to-columns, cell formatting, and other methods.
but, I was unable to alter the column in sheet 2. I tried:
- Cell Formatting
- VALUE(text), CLEAN(text), TRIM(text), TRIM(CLEAN(text)), CLEAN(SUBSTITUTE())
- Multiply by 1
But only the cell with the green-mark changes to a number, the rest stays the same. I browsed the internet but didn't get a solution either.