String – Numeric value issues

Topic

New Reply

I have an export from a database in the form of a *.csv file that I am comparing to an excel spreadsheet. It used to work fine with for – next loops stepping through the various values & flagging differences. Recently the application that generates the *.csv values was updated and now instead of cells containing “Some Value”, I get cells with formulas of “=Some Value”. I am told this cannot be changed. My work-around was to select all cells and do a copy / paste special: values and this clears all the =” “, leaving just the value, but I believe numeric values are still “text” (remain left justified) and when the comparisons are made from the spreadsheet’s numeric values against these, the *.csv cell value is “Value” is seen as different (ie. 12345 VS “12345”). I tried to programatically format to a numeric (Selection.NumberFormat = “0”), but no luck. The weird thing is that if I click into the cell and then accept (check) with no changes the cell converts to numeric and all is well with the macro after that. This is also true if I click on the “green formatting issue” triangle and select convert to number, but how to do this programatically? Use the value Command? How?

Reply | Quote

Replies

You could try this piece of code, highlight the cells you want formatted

[codebox]Sub StringToInteger()
Dim y As Integer
Dim x As Range
Dim z As Range
Set z = Selection
y = 1
Set x = Range(“A65536”).End(xlUp).Offset(1)
If x “” Then
Exit Sub
Else: x.Value = y
x.Copy
z.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply
Application.CutCopyMode = False
End If
x.ClearContents ‘Back to normal

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

End Sub[/codebox]

Reply | Quote

Or slightly simpler

Code:

Sub StringToInteger()
  With Selection
	.NumberFormat = "General"
	.Value = .Value
  End With
End Sub

Reply | Quote

This is also true if I click on the “green formatting issue” triangle and select convert to number, but how to do this programatically? Use the value Command? How?

NON VBA

When I get data from a source that has values a text, my fast conversion is to find an empty column and put in the formula = A1 * 1 and copy down. A1 in the formula is the location of the cell with the text value.

In most cases this should convert your text values to actual numbert. If you don’t want the formulas just select the entire range with the forumulas and do a copy paste special (select values) and copy to the same location, that will change all the formulas to valules.

Regards,

Tom Duthie

Reply | Quote

You might also try TextToColumns, setting format General in the last dialog screen, to do a quick conversion from text to number.

Reply | Quote

All,

Thanks much for the imput, it was helpful. In the end, because I was only comparing and not saving one of the two files, I handled the issue within the VBA code by using the “Format” command. To compare the “text” values from the “disposable” workbook with the original workbook’s number values, I used “If Cells(CSV_Row, 10).Value = Format(PN) Then” (the default output of Format is text). In one case, the string had a decimal, while numerical value did not. Here I concatenated the missing “.0” as it was always a whole number (ea.) Ex: “If Cells(CSV_Row, 2).Value Format(Qty) + “.0″ Then”. I originally tried the Str command, but it adds a space prefix to hold the number’s sign so an extra complication.

Reply | Quote

As an alternative to Str you could use CStr; this does not add a leading space.

Reply | Quote