0

I have a column full of numbers, but they aren't stored as numbers.

For most of them, Excel automatically offers to convert them to numbers (I have the green error indicator in the upper left hand corner of the cells). For a few, though, it doesn't and I don't see how they differ from the rest. Normal formatting (Home -> Number) doesn't change anything.

I tried using the function value(), doesn't work. I also tried to PasteSpecial the formats of another cell onto those that are giving me trouble. And I tried to multiply all of the cells with 1, but again the troublesome cells remain unchanged. What can I do?

I imagine it'll difficult to help me without the data, so here's the link to the sheet

edit: As Tom Brunberg pointed out, there's a new line in some of the cells that causes the problems. I removed them via VBA: Workbooks("Workbook name").Sheets("Sheet name").Range("M1:P70").Replace Chr(10), "", xlPart

Improve this question
6
  • Why not just select the range, right-click > properties > format cells and change them all to Number?
    – spikey_richie
    Jun 5, 2019 at 7:05
  • @spikey_richie I forgot to mention it, the normal formatting doesn't work either.
    – Alex
    Jun 5, 2019 at 7:13
  • 1
    The cells you have problems with have a new line character after the numbers. You can easily see it in the formula/value edit field by clicking the drop down button at the right end of the field. Then just click beneath the number. Remove the new line character and convert to number. Tip, you can convert the whole column A in one go, after you have removed the new line characters.
    – Tom Brunberg
    Jun 5, 2019 at 8:19
  • Thank you! I'm going to remove it with VBA because I have more data than what I've put in the file.
    – Alex
    Jun 5, 2019 at 8:39
  • @TomBrunberg Oh sorry, I didn't want to take away your (well-deserved) credit. You really helped me out there, not sure when I would've discovered the issue.
    – Alex
    Jun 5, 2019 at 11:40

1 Answer 1

Reset to default
1

You can use the function CLEAN(CELL), which removes the "New Line (\n)" from the string.

=CLEAN(CELL)

Then, VALUE(XX) should work :)

Improve this answer
1
  • The extra character is known to be LF, so this is a good solution for that. It might be useful to add a couple of sentences about the limitation of CLEAN. CLEAN removes the first 32 nonprinting ASCII characters (values 0 through 31). In Unicode, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157), which CLEAN won't remove. So readers should be aware that CLEAN won't solve every problem involving nonprinting characters, or leading or trailing spaces.
    – fixer1234
    Jun 5, 2019 at 20:49

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .