VBA: преобразовать текст в число

У меня есть столбцы чисел, которые по любой причине отформатированы как текст. Это мешает мне использовать арифметические функции, такие как функция промежуточных итогов. Как лучше всего преобразовать эти "текстовые числа" в истинные числа?

Вот скриншот конкретной проблемы: Error

Я попробовал эти фрагменты безрезультатно:

Columns(5).NumberFormat = "0"

а также

 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Ответы

Ответ 1

Используйте описанную ниже функцию (изменив [E:E] на соответствующий диапазон, соответствующий вашим потребностям), чтобы обойти эту проблему (или измените на любой другой формат, например, "мм/дд/гггг"):

[E:E].Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
End With

PS По моему опыту, это решение VBA работает ЗНАЧИТЕЛЬНО быстрее на больших наборах данных и с меньшей вероятностью приведет к сбою Excel, чем при использовании метода "окно предупреждения".

Ответ 2

Это можно использовать, чтобы найти все числовые значения (даже те, которые отформатированы как текст) на листе и преобразовать их в одиночные (функция CSng).

For Each r In Sheets("Sheet1").UsedRange.SpecialCells(xlCellTypeConstants)
    If IsNumeric(r) Then
       r.Value = CSng(r.Value)
       r.NumberFormat = "0.00"
    End If
Next

Ответ 3

У меня была эта проблема раньше, и это было моим решением.

With Worksheets("Sheet1").Columns(5)
    .NumberFormat = "0"
    .Value = .Value
End With

Ответ 4

Это преобразует весь текст в столбцах книги Excel в номера.

Sub ConvertTextToNumbers()
Dim wBook As Workbook
Dim LastRow As Long, LastCol As Long
Dim Rangetemp As Range

'Enter here the path of your workbook
Set wBook = Workbooks.Open("yourWorkbook")
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastCol = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column

For c = 1 To LastCol
Set Rangetemp = Cells(c).EntireColumn
Rangetemp.TextToColumns DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True
Next c
End Sub

Ответ 5

Решение, которое для меня работает:

For Each xCell In Selection

  xCell.Value = CDec(xCell.Value)

Next xCell

Ответ 6

Для больших наборов данных требуется более быстрое решение.

Использование функции "Текст в столбцы" обеспечивает быстрое решение.

Пример на основе столбца F, начальный диапазон от 25 до LastRow

Sub ConvTxt2Nr()

Dim SelectR As Range
Dim sht As Worksheet
Dim LastRow As Long

Set sht = ThisWorkbook.Sheets("DumpDB")

LastRow = sht.Cells(sht.Rows.Count, "F").End(xlUp).Row

Set SelectR = ThisWorkbook.Sheets("DumpDB").Range("F25:F" & LastRow)

SelectR.TextToColumns Destination:=Range("F25"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
    Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
    :=Array(1, 1), TrailingMinusNumbers:=True

End Sub

Ответ 7

Используя ответ aLearningLady выше, вы можете сделать свой диапазон выбора динамическим, ища последнюю строку с данными в ней, а не просто выбирая весь столбец.

Код ниже работал для меня.

Dim lastrow as Integer

lastrow = Cells(Rows.Count, 2).End(xlUp).Row

Range("C2:C" & lastrow).Select
With Selection
    .NumberFormat = "General"
    .Value = .Value
End With

Ответ 8

Хотя Text to Column - хороший способ сделать это, Вы можете использовать его только в одном столбце. Если вы попытаетесь выполнить решение Text to Column, вам следует использовать его только по одному столбцу за раз.