CODEWITHSUNDEEP
vbaexcelcsvdecimal
Cornelis
Cornelis

Reputation: 435

Save as .csv correct decimal separator

I'm writing a "master" Excel document that creates new workbooks and saves them as ".csv" by using Visual Basic for Applications. The created workbooks contain measured data from two sources. One source already delivers the measure date in my regional settings (Holland, in which the comma , sign is commonly to used as decimal separator). The other source however delivers data with a dot . as separator (American settings). I'd like to replace all dot signs for comma signs.

Is this possible with the Replace function?

(I guess this solution would only fail when applying it on values greater than 1.000,00 (or 1,000.00 for US settings). Which would be acceptable but far from perfect.

Attempts so far

In Addition

Excel thinks the values from the text document have 1000 separators 31.435. When i turn of the 1000-separator the value is 31435. I need it to be 31,435 (decimal Separated by comma , sign)

Do I need to adapt the cell format?
If so, then... How?
Any other options to solve this?

Anwser/Sollution(that works best for me)

Sheets(SheetNaamCNC).Range("B1").Select
Do While ActiveCell.Value <> ""
    If InStr(ActiveCell.Value, "0,") = 0 Then
        ActiveCell.Value = ActiveCell.Value / 1000
    End If
    ActiveCell.Offset(1, 0).Select
Loop

Upvotes: 1

Views: 7074

Answers (4)

Cornelis
Cornelis

Reputation: 435

Already accived to set , as separator. This works for Values like 0,234 but values like 31,345 will fail. They are displayed as 31.345 (with in this case the . as thousand separator). Possible Sollution Loop through cells one by one. If first charachter does not equals "0" then devide ActiveCell.Value by 1000. Kind of a detour... but I guess it will work for me.

Add the following, 

Sheets("YourSheetName").Range("TopOfRangeYouLikeToEdit").Select
Do While ActiveCell.Value <> ""
    If Left(ActiveCell.Value, 1) <> "0" Then
        ActiveCell.Value = ActiveCell.Value / 1000
    End if
    ActiveCell.Offset(1,0).Select
Loop

-Edit by Cornelis: Instead of Left(ActiveCell.Value,1)<> "0" 

If ActiveCell.Value < 0 Then

would be better

Upvotes: 0

nekomatic
nekomatic

Reputation: 6284

When you read a text file in to an Excel sheet using the Data tab > Get External Data > From Text, you can specify the decimal point and thousands separator by clicking the Advanced button in step 3 of the text file import wizard.

enter image description here

If you record a macro while you import one of your data files, you'll get something like this: 

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;myfilename.csv" _
    , Destination:=Range("$A$1"))
    .Name = "myfilename"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
    .TextFileDecimalSeparator = ","
    .TextFileThousandsSeparator = " "
    .Refresh BackgroundQuery:=False
End With

and you can then use the recorded code to develop your own macro that will import future data files in the correct format - see in particular the TextFileDecimalSeparator and TextFileThousandsSeparator properties.

Upvotes: 2

Estevam Garcia
Estevam Garcia

Reputation: 445

You could use a function like below

Sub sub1()
    Application.DecimalSeparator = "."
    Application.ThousandsSeparator = ","
    Application.UseSystemSeparators = False
    Plan1.Columns(1).NumberFormat = "#,##0.00"
End Sub

Upvotes: 2

Tom Sharpe
Tom Sharpe

Reputation: 34180

If it's reading them in as text, you could try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(sheet1!A2,",","|"),".",","),"|",".")

If I was doing it this way I would enter this formula in the same column of a new sheet for every column I wanted to re-format, and just copy any other columns using

=sheet1!B2 etc.

It depends how many columns you have whether this would be a workable solution.

Upvotes: 1

Related Questions