Unable to convert text to Numbers

0 votes

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.

![[enter image description here enter image description here

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.
enter image description here

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.
enter image description here

Dec 22, 2022 in Others by Kithuzzz
• 28,900 points

1 answer to this question.

0 votes

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:

enter image description here

answered Dec 23, 2022 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

Convert text date/time to a real date time in excel

For a date conversion: =DATEVALUE(TEXT(A1,"MM/DD/YYYY")) For a time conversion: =TIMEVALUE(TEXT(A1,"HH:MM:SS")) For ...READ MORE

answered Dec 19, 2022 in Others by narikkadan
• 53,520 points
0 votes
1 answer

Excel Conditional Formating to find numbers a cell with text

Try this: =OR(ISNUMBER(-MID(SUBSTITUTE(A1," ","~")&"~",seq,4))) where seq is a defined name that ...READ MORE

answered Jan 10 in Others by narikkadan
• 53,520 points
0 votes
0 answers

unable to connect to internet in edureka VM

i am unable to connect to internet ...READ MORE

Jun 7, 2019 in Others by Ashok
• 120 points
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
0 votes
1 answer

How to define excel SUMIFS criteria considering any text and numbers?

It essentially ignores column C's missing entry ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 53,520 points
0 votes
1 answer

Convert text to number in Google Sheets & Excel

Try: =INDEX(IF(REGEXMATCH(A1:A4&""; "M"); REGEXEXTRACT(A1:A4; "\d+.\d+|\d+")*1000000; IF(REGEXMATCH(A1:A4&""; "k"); ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 53,520 points
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP