Convert text to number in Excel in VBA

Question

For some reason Excel is converting my number into text and adding a preceding apostrophe in every cell in column E3 and F3.

I need to convert columns E3:F3 back to numbers and format them to currency. How do I do that?

A1:K2 is the header.

The code below is not working:

Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
wb.Sheets(1).Rows(2).Delete
wb.Sheets(1).Range("E3:F3") = Mid(wb.Sheets(1).Range("E3:F3"), 2,
                              Len(wb.Sheets(1).Range("E3:F3")) - 2)
wb.Sheets(1).Range("E3:F3").NumberFormat = "$#,##0.00"

WireTalents · Answer 1 · 2015-01-16T04:39:35.280

If your text is only a number, the answer is simple. Multiply it by 1.
Say cell A1= '00001234 or a formula or macro returns a piece of text, multiply the answer by 1. "00001234" * 1 = 1234.
I want to extract the value of a Label or a txtBox on a VBA form.
Num = lblText * 1
Another example: If .txtLevel10 * 1 > 50 Then...etc.
Also works with some other data types "16-Jan-15" *1 = 16/1/15
Works in Excel & VBA but only if there are no other characters in the original text. Cheers

score 1 · Answer 2 · answered Aug 23 '11 at 18:50

1

Try:

Range("E3:F3").Style = "Currency"

answered Aug 23 '11 at 18:50

James

2,454
1
22
22

Did not work but thanks. – Bruno Aug 23 '11 at 19:06

BradC · Accepted Answer · 2011-08-23T19:46:29.310

1

Assuming you want the same currency formatting you get from the toolbar, this works:

wb.Sheets(1).Range("E3:F3").Formula = wb.Sheets(1).Range("E3:F3").Value
wb.Sheets(1).Range("E3:F3").Style = "Currency"

Just using worksheet.Range() with no properties forces Excel to guess exactly which property you actually mean (this is called the "default property"), and you get inconsistent results.

edited Aug 23 '11 at 19:46

answered Aug 23 '11 at 19:39

BradC

39,306
13
73
89

Nice. It did work. How do you get the entire column to be formatted that way except E1,E2,F1,F2 (which is the header of the column)? Thanks! – Bruno Aug 23 '11 at 19:52
You'd probably have to do something like `Range("E:F").Style = "Currency"` followed by fixing the headers: `Range("E1:F2").Style = "Normal"` – BradC Aug 23 '11 at 19:56
This seemed to have worked:`wb.Sheets(1).Range("E:F").Formula = wb.Sheets(1).Range("E:F").Value wb.Sheets(1).Range("E:F").Style = "Currency"` – Bruno Aug 23 '11 at 20:01

transistor1 · Answer 4 · 2011-08-23T19:38:07.533

0

Try highlighting that column and doing Data->Text To Columns (Excel 2003; in 2007+ Text to columns is on one of the ribbons). Click 'Delimited', then 'Next', Next again, then select General as the format. This should convert the whole column into a number.

If this works, it is easily automated. Let me know if you need code.

EDIT - you have to do one column at a time.

edited Aug 23 '11 at 19:38

answered Aug 23 '11 at 19:27

transistor1

2,915
26
42

Igor Turman · Answer 5 · 2011-08-23T20:05:50.257

This, actually, works. The key is to apply format before setting the value:

Set wb = objApp.Workbooks.Open("aFile.xls", True, False)
wb.Sheets(1).Rows(2).Delete

wb.Sheets(1).Range("E3:F3").NumberFormat = "$#,##0.00"

For Row = 3 To 3 'your rows range in case you need iterate through (1 row only in your case)
    For Column = 5 To 6 'E3:F3
        wb.Sheets(1).Cells(Row, Column) = Mid(wb.Sheets(1).Cells(Row, Column), 2, Len(wb.Sheets(1).Cells(Row, Column)) - 2)
    Next Column
Next Row

score 0 · Answer 6 · answered Aug 23 '11 at 19:43

Len(wb.Sheets(1).Range("E3:F3"))

For me this just (as expected) throws an error. You'll have to process each cell individually to use your approach.

Dim c as Range
Set wb = objApp.Workbooks.Open("aFile.xls", True, False)    

With wb.Sheets(1)
   .Rows(2).Delete    
   For each c in .Range("E3:F3").cells
     c.Value = Mid(c.value, 2, Len(c.value)-2) 
     c.NumberFormat = "$#,##0.00"
   next c
End With

Convert text to number in Excel in VBA

6 Answers6