Converting data stored as text to numbers (Oracle SQL)

Question

I have some data that i pull from a couple of Oracle databases into Excel via a VBA macro. Some of the columns have data (numbers), afaik, stored as text. I need to convert these "wrongly" formatted data to numbers so that i can do calculations on it, however i cant figure out how to do this effectively using VBA. Furthermore only some of the data is stored as text (columns K-Q are formatted incorrectly, see picture below).

Currently im using this loop to convert the data:

Sub convertTextToNumbers(ByVal sColumnHeader As String)
    Dim col As Range, c As Range
    Dim colIndexNumber As Integer, lastUsedRow As Integer

    colIndexNumber = findColumnIndexNumber(sColumnHeader)

    lastUsedRow = ActiveSheet.Cells(Rows.Count, colIndexNumber).End(xlUp).Row

    Set col = Range(Cells(2, colIndexNumber), Cells(lastUsedRow, colIndexNumber))

    For Each c In col
        c.Value = c.Value
    Next c
End Sub

Is this the most effective way to do it? Can i convert using SQL? The SQL-query im using is simply:

SELECT * FROM table_name

Also converting by choosing another formatting does not work.

Thanks!

Data wrongly formatted is in columns K-Q:

use the to_number sql function to convert the columns needed. — OldProgrammer
– OldProgrammer, Commented Sep 17, 2015 at 15:47
just wondering why don't you make an ODBC connection and use it in Excel? — user206168
– user206168, Commented Sep 17, 2015 at 16:11
OldProgrammer i think this is exactly what i need. I also found the to_date and to_timestamp functions user206168 i am pulling the data from an ODBC connection, giving me the above MatthewD the formats in K - N and P - Q are supposed to be timestamps. If i do eg. =K10+K11 it will work, however =SUM(K10:K11) does not work. — M. Andersen
– M. Andersen, Commented Sep 17, 2015 at 16:26
You should be able to pull them as a timestamp with to_timestamp. Note if you use todate you may have to include the timestamp IE 2015-01-01 59:59:59. If not you can apply the timestamp to the cells using the number format. — MatthewD
– MatthewD, Commented Sep 17, 2015 at 16:38

MatthewD · Accepted Answer · 2015-09-17 16:17:25Z

1

Use number format on the columns.

Columns("K:Q").Select
Selection.NumberFormat = "0.00"

Or more programmatic

Private Sub formatNumbers()
    Dim ws As Excel.Worksheet

    Set ws = Application.ActiveSheet
    ws.Range("K:Q").NumberFormat = "0.00"

End Sub

Any format you see in the range format menu can be applied this way.

edited Sep 17, 2015 at 16:17

answered Sep 17, 2015 at 15:46

MatthewD

6,8215 gold badges24 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatthewD Over a year ago

Are you applying it before you write the value to the cell?

M. Andersen Over a year ago

No it is applied afterwards

Collectives™ on Stack Overflow

Converting data stored as text to numbers (Oracle SQL)

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related