Unable to convert text to Numbers

Question

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:

• ext-to-Columns
• 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.

Answer 1

According to the comments, there may be extra characters in your digit strings that you can't convert to text that can't be removed by TRIM or CLEAN.

• Determine what those characters are
  • Assume a "non-convertible" digit string is in A1
  • Enter the following formula
    • B1: =MID($A$1,ROWS($1:1),1) and fill down
    • C1: = UNICODE(B1) and fill down
  • From this, you can determine the character to use in a SUBSTITUTE function.

For example: