Number stored as text

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
Hello people,

When the macro is executed, all my values is stored as text, is there a simple code that can convert the numbers stored as text into values, although i can see they are values i cannot use the sum function since the values are stored as text and each cell which has a value is flagged green.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

KWMSeattle

Board Regular
Joined
Aug 23, 2006
Messages
149
Would you mind posting your code? We can help you add to it to format numbers as numbers.
 

Katy Jordan

Well-known Member
Joined
Jun 28, 2008
Messages
596
here is my code

Code:
Sub Example()
GetCSV Date, 2, 4
End Sub
 
Sub GetCSV(ForMonth As Date, Column1 As Integer, Column2 As Integer)
Dim ws As Worksheet, myDir As String, fn As String, temp As String, wsName As String
Dim fso As Object, x, y, a() As String, i As Long, n As Long, t As Long
 
Column1 = Column1 - 1
Column2 = Column2 - 1
 
Set fso = CreateObject("Scripting.FileSystemObject")
myDir = "U:\NRFF"
wsName = "Interest-" & MonthName(Month(ForMonth) - 1, False) & ".xls"
On Error Resume Next
Set ws = Workbooks(wsName).Sheets("downloads")
If ws Is Nothing Then _
    Set ws = Workbooks.Open(myDir & "\" & wsName).Sheets("downloads")
On Error GoTo 0
If ws Is Nothing Then
    MsgBox myDir & "\" & wsName & " is not found"
    Exit Sub
End If
t = 1
fn = Dir(myDir & "\*.csv")
Do While fn <> ""
    Set myTxt = fso.OpenTextFile(myDir & "\" & fn)
    temp = myTxt.ReadAll: myTxt.Close
    x = Split(temp, vbCrLf)
    ReDim a(1 To UBound(x) + 1, 1 To 2)
    For i = 0 To UBound(x)
        y = Split(x(i), ",")
        If UBound(y) > 0 Then
            n = n + 1: a(n, 1) = y(Column1): a(n, 2) = Val(y(Column2))
        End If
    Next
    With ws.Cells(1, t)
        .Value = fn
        .Offset(1).Resize(n, 2).Value = a
    End With
    t = t + 2: n = 0
    fn = Dir
Loop
Set fso = Nothing: Set ws = Nothing
End Sub





Would you mind posting your code? We can help you add to it to format numbers as numbers.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,688
Messages
5,597,546
Members
414,154
Latest member
thevaper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top