Converting text to number

[tw15ns]

SIMs exports numeric data to Excel as text (very helpful). Normally, for my own use, I just convert it to numeric using Excels error checking feature. However, I need to write a report for another member of staff and so want to automate this convertion process using a macro. Does anyone know what VBA code I could use to do this.

 

Cheers

[DrPerceptron]

You can use Excel to generate a Macro for you just by doing what you would usually do and recording it - it'll generate it as viewable VBA and you can haxxor it should you feel the need.

 

I'm not big on reports from SIMS, but can you not just highlight the column/rows and change the formatting options from text to numerical (or whatever type you want)?

[tw15ns]

Tried that already but it doesn't record error checking for some reason. Thanks anyway

[laserblazer]

Select the text , right click, format cells, change to number. Should be able to get a macro to do that. Is that what you wanted to do?

[mac_shinobi]

depending on what numerical data type you wanted to convert to

 

Excel VBA Type Conversion Functions. Convert Numbers to Text & Text to Numbers

 

converting to int

[b]Sub ConvertToInteger()[/b]

Dim vStr, intNum



vStr = "1000.5"

intNum = CInt(vStr)

MsgBox intNum


[b]End Sub
[/b]

You could alter the above to something like so

 

[b]Sub ConvertToInteger(byval intNumber)[/b]

Dim intNum

intNum = CInt(intNumber)

ConvertToInteger = intNum

[b]End Sub[/b]

Otherwise as per above just use a macro to record it by formatting the cell into a number type cell