You know the story: you go to the web and download some data. It looks good so you reorganise it, add some formatting, maybe even turn it into an Excel Table and then begin thinking about a Pivot Table, regression analysis.
Then you notice: hmm, that third column looks different: it’s left aligned, not right aligned. Ah! It’s numbers as text … you look in the cell and find there is an extra character at the end of the number … easy, you think … I’ll just use =TRIM().
Oh! That failed!
I know, I’ll put 1 in a cell, copy it and then paste special Multiply all of the affected cells … Oh! That failed too! I thought about =LEFT() … RIGHT() and so on but wasn’t clear in my own mind what to do: well, it is Sunday morning!!
So, I’ll go to the web, someone there will know what to do as I often come across this problem and then forget the answer!
Go to the Web
I went to a Microsoft page that had load of options =VALUE() … CLEAN() … TRIM() … do this, do that. None of them worked.
I went to other non Microsoft pages and the old, old story came back: in some cases, no one knows what to do. Until I thought about this …
LEN(): the knight in shining armour
In a blank cell I entered =LEN(G6) and even though G6 contained what looked like 0.5, length of 3, LEN told me length of 4 for cell G6. Since I knew there was that extra character at the end of 0.5 and all other numbers in that column, I did this
That gave me 0.5 in my new cell: I filled that formula down for the rest of that column and now had what I wanted … well, nearly! In a smart tag, Excel gave me an error message that said that my numbers were stored as text so, making sure I had the entire column selected I clicked the smart tag and one of its options was Convert to Number so I clicked that and bingo! All of my numbers are now numbers!
Maybe not for you
That was a lot of words and if you are dealing with, say, just 10 or 20 numbers, don’t waste your time, just over type the cells with the number you are expecting: that is very direct and it works. My column was 1,000 rows deep and no one has the time to retype 1,000 numbers when they don’t have to, do they?
I don’t say that the Microsoft and other pages I found were useless, that’s not fair. It just so happens that their solutions don’t fit every case. My solution here works with an extra character that LEN() will help you to identify and get rid of.