Unable to convert text to number

I have the attached part of Column A of data that is part of bigger worksheet that I need to convert to number to run some analysis; however, I cannot figure out with the typical methods on how to ch...

excel

Formulas and Functions

Patrick2788

Silver Contributor

Mar 09, 2023

It's possible there's a character in the cells that's causing the numbers to be treated as text. This character may look like a space but could be another ANSI character. I'd inspect one of the cells by hand to see if there are leading or trailing 'spaces'. If so, copy the character and then do a find/replace to remove it.

Gatill3ro

Copper Contributor

Mar 09, 2023

Thanks Patrick2788

I reviewed the data manually and there where no symbols, period or blanks spaces, but to follow your suggestions. I did a =RIGHT and =LEFT functions to remove any invisible pre-leading digits and found that although I could not see anything I guess there was some type of digit/space before and after the text.

EXAMPLE, MY A2 CELL WAS 0001

To clean left side of A2, in CELL D2, I used =LEFT(A2,LEN(A2)-1) which returned with 0001; that indicated that there was a pre-leading digit/blank on the left side that was not visible, but the formula removed it. Them, in Cell F2, I used =RIGHT(D2,LEN(D2)-4) to clean the right side of D2 which already had the left side cleaned; I was able to confirmed that there was also a digit/space in the right side of the cell since -3 should have returned 1, but it required -4 to return 1; still as text. In Cell G2, I used =VALUE F2 and "wah lau" I got 1; the desired, text to number conversion; super hard way, but I can't control the original data source so this will have to do. Note that the =RIGHT formula was modified to -3 when I reached 10, and -2 when I reached 100, -1 when I reached 1,000. I appreciate the feedback!

Forum Discussion

Unable to convert text to number

Resources