9

I get sent a spreadsheet weekly, and for various reasons beyond my control some columns come out as text stored as numbers rather than numbers. I need to convert them to numbers for stuff that happens with them later in the code.

I am converting them to numbers at the moment by doing this:

Dim rng As Range

For Each rng In Range("A:D").Columns
   rng.TextToColumns
Next rng

Is there a better (i.e. more efficient) way of doing this?

I played around with NumberFormat and it didn't seem to work.

Thanks in advance (and apologies if I have missed a solution already here - I did a search and didn't find it).

Community
  • 1
  • 1
EndlessLoop
  • 424
  • 1
  • 6
  • 15
  • 5
    I would do like this : rng.Value = rng.Value. It should convert the text to numbers. Just a small glitch though, if you have any formula in the range, it would convert the formula to plain value as well. There are easy workaround for this problem though. But that should do the trick. – Vikas Aug 22 '13 at 08:39
  • 1
    Note, that @Vikas' method doesn't work with all locales, depending on their respective decimal separator. For example in the German locale, if you apply the `.Value = .Value` to a range, only the integers will be converted to numbers. That is a range with the text content: 1 | 2 | 0,5 becomes *1* | *2* | **0,5** where the italic parts are converted to numbers but the bold part is still only text. – Marcus Mangelsdorf Nov 02 '15 at 17:00

3 Answers3

11

Excel:

  • Copy an empty cell
  • Select the range in which you need to change the format
  • Select PasteSpecial, and under Operation select Add

VBA

(change Sheet1 Accordingly, assuming that A100000 is empty):

Sheet1.Range("A100000").Copy
Sheet1.UsedRange.PasteSpecial , xlPasteSpecialOperationAdd

If you place the above into the Workbook_Open event, it will perform the conversion automatically every time you open the workbook.

With this method, formulas are preserved.

I hope that helps!

Ioannis
  • 5,238
  • 2
  • 19
  • 31
  • Vikas and loannis: great techniques for this situation - thank you. I have tested both suggestions and they work very well for my needs. – EndlessLoop Aug 22 '13 at 22:54
  • The great advantage of that technique is that the empty cells remain empty. – iDevlop Feb 01 '16 at 13:19
9

From this website http://www.access-programmers.co.uk/forums/showthread.php?t=219316 I got this idea:

Range("D1:D400") = Range("D1:D400").Value

And it works perfectly!

ZygD
  • 22,092
  • 39
  • 79
  • 102
Helfenstein
  • 315
  • 1
  • 4
  • 13
  • It's the same as [@Vikas](http://stackoverflow.com/users/723914/vikas) suggestion in the comments below the question: `rng.Value = rng.Value`. However, this does not work for me while `TextToColumns` and `PasteSpecial` do work. – ZygD Sep 06 '15 at 23:24
  • 1
    This is way faster than going through each cell. Much better for large amounts of data. –  Jul 24 '18 at 13:32
1

This is easier:

With Selection
    .NumberFormat = "0"
    .Value = .Value
End With
peak
  • 105,803
  • 17
  • 152
  • 177
DeepikaShinde
  • 31
  • 1