Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Convert decimal with comma to point Results

I was making a form where the user could enter a number; eg. 4.61

However, depending on the user's background he might enter this as 4,61 (comma) or as 4.61

In the form this variable comes in as a string. How can I convert this in VBA to a digit if I don't know in advance if the users system uses a . or , as the digit-seperator??

Any help would be very grateful

I thought that in access you can actually predefine the type that is entered into the form but I don't believe this is possible in Excel (or at least i could not find something like that)

thanks for the help

---

I have mulitple cells with data in the format 153,95 and I need to convert
these to 153.95. Please help

So I'm hoping I'm just being an idiot and there is an easy fix!
I'm using a program, GIS (Geographic Information System) and I need to get data from a pdf into a txt file to upload into the GIS program.
I have a pdf with tables in it; it's countries and number of visits (page 3)
When I copy and paste it (even using formatting) it doesn't copy and paste the right columns. I did convert into a spreadsheet, which was great, BUT:
it's Costa Rican data, and they use the period instead of a comma (ex. 5,406 is 5.406)
SO when I export the data into excel, it's in decimal points. I tried finding all the decimals and replacing them with commas, but even before that my data was messed up... numbers like 4.950 (ending in zero) turn to 4.95, so when I change the commas it's just 4,95. Now I know this is an easy fix, just go through and spot the ones that look like that, but I need to export a few of these files and I don't want errors and I can't afford the time it will take me.
Sooooo is there a way to tell it that 1)they are supposed to be commas and 2)I don't want them to drop the zero? I've tried adding decimal points but that just messes it up even more... eeek! This is so frustrating!
Also, while I'm asking, when I export it into a spreadsheet I think it keeps some of the table formatting (is that true) I always get a message telling me that some of it can't be formatted with older versions or something, and hit yes to save anyway. I've tried saving this into a txt file but when I enter it in GIS it doesn't work... is there a way to make it simple text, or something? Like remove the format? If that even makes sense?
Thanks in advance. I was pulling my hair out for two hours today trying to figure it out!

I've just imported a file into a UK version of Excel from a German made datalogger. The decimal points (which should look like this 0.0) all appear as comas (0,0) with the result that Excel can't read them.

How do I change the comas into decimal points so that they are readable?

Hi, I have received a file with a comma instead of a decimal point. How do i
convert 4,56 to 4.56?

I have an Excel file that is generated from an SQL db, through an OpenXML request.
All fields are exported as strings.

Depending on the customer's configuration, sometimes the decimal separator is a comma, sometimes a point.

When the Excel file is first open, some code runs to clean and normalize the data, especially to convert numbers and dates properly.
I check for the local decimal separator (from the regional settings) and replace whatever separator is used in the file with it.

This is where I have a problem.

Let's assume my separator is a comma.
If the file already has commas, then it's fine, nothing changes.
If it's a point, sometimes it works, sometimes not.

If the number I want to convert has 2,3,4 decimals, it converts fine.
More than that and it goes crazy.

A number like 32.12345678 will be converted to 3212345678 (the point is not replaced, it is even removed!)
The same number but 32.12 will be converted to 32,12 just fine.

This is driving me crazy. By the way I used the replace function to change the string in complete ranges, much faster.

Any help is appreciated!!

I have written a Matlab program to do some data analysis. The results are then exported to an Excel-file. I'm am planning to distribute this Matlab-script among my colleagues. However, some of their computers will use the point as a decimal separator (English system locale), whereas others use the comma (Dutch system locale). Matlab exports its data with a point as decimal separator, which gives incorrect results if the program is run on systems with the comma separator.

I have noticed that numbers with decimals, in an Excel-file created on a system using a decimal point, are automatically converted by Excel when the file is opened on a system with a decimal comma (so 2.56 becomes 2,56). So the Excel-format must include some information on the decimal separator or locale of the system at the last save (otherwise Excel wouldn't know that it had to convert the numbers). I am wondering whether I can set this decimal separator or locale myself for the exported Excel-file? This way, an Excel-file created by Matlab on a system with a comma as separator will be recognized as an Excel-file that was created on a system with a decimal point. Then Excel should automatically convert the numbers.

I have an Excel application that is programmed in English (USA). I have a couple of users who use their system in Portuguese. When the app. is run on their machines REGARDLESS of the language setting they're using, the below variable sngBreak ALWAYS returns a value formatted in Portuguese, for example, if the cell contains 1.5 (language setting to English) the variable contains 1,5; if the cell contains 1,5 (language setting Portuguese) the variable still contains 1,5.

Sample code:
Dim sngBreak As Single
...
    With ActiveCell
        sngBreak = .Offset(i, BREAK_CELL).Value
    End With
...

I've tried the below code when the workbook opens and it doesn't affect the VBA variable settings:
With Application
    .ThousandsSeparator = ","
    .DecimalSeparator = "."
    .UseSystemSeparators = False
End With
This works visually in the spreadsheet cells just fine but it doesn't affect the internal VBA variable representation of the number. Also, so no one will mention it, trying to Replace commas with decimal points only affects the visual contents of the sheet i.e., it doesn't work, it's not a solution.

How do I get the sngBreak variable to register its values as English? Is there a way to force Excel to run as English (maybe a solution)?

I have almost 1,000 numbers that are formatted to display in Excel as
currency with dollar signs, commas, decimal points, and cents (for example,
1234.5 is displayed as $1,234.50). I need to use them in a merged Word
document (a pledge payment letter) but can't figure out how to "convert" them
in Excel from numbers to text AND still retain the displayed "dollars look"
of the numbers.

Would appreciate a solution. Thanks!

I am specifically having trouble setting (and getting) the series object properties for Excel versions other than English. Currently I set the Chart Series object with a VBA command like:

Series.Formula = "=SERIES(,Sheet1!R17C2:R19C2,Sheet1!R17C3:R19C3,1)"

I am assuming in languages that use a comma "," as a decimal point the command can not be used to separate parameters in Excel commands so I attempt to set it as such using a semicolon as a delimiter:

Series.Formula = "=SERIES(;Sheet1!R17C2:R19C2;Sheet1!R17C3:R19C3;1)"

I am also curious about how to set via VBA Excel range object in languages other than English. I am assuming a range my. In the above example the range reference “Sheet1!R17C2:R19C2” may not be valid for non English alphabet???

I currently get the series object range setting by puling apart the string returned by the formula Series.Formula command this should be something like above. The trouble also here is I don’t know for sure what the delimiter (comma or semicolon) is for each country. I make an assumption based on cdbl(“2,2”) to see if it convents it to 2 or 2.2. If it converts to 2.2 them I assume I need to use a semicolon as a delimiter as the comma is used as a decimal point.

I am finding that in Germany my program wont work. It gets a problem when it attempts to set the Series formula

i.e.

Series.Formula = "=SERIES(;Sheet1!R17C2:R19C2;Sheet1!R17C3:R19C3;1)"

It get error “series formula can not be set” (in german: Die Formula-Eigenschaft des Series-Objektes kann nicht
festgelegt werden.)

Any pointers in the right direction would be much appreciated.

I would like to find out if there is any where developers can go to learn about developing to make application run in multiple languages.

I'm having a little "fun" creating a CSV file from an ASP page for use with
Excel and hope someone here knows of a trick that doesn't involve user
intervention.

I am familiar with the format of CSV files - strings containing a comma must
be enclosed with double quotes, otherwise just separate all the columns with
a comma.

But ...

Excel tries to be smart about things. If a string, quoted or unquoted,
contains leading spaces and/or trailing spaces and/or leading zeros and is
otherwise numeric (number groupings, decimal points, positive/negative sign,
currency sign, and the characters 0-9) it converts the value to numeric.

For example, the CSV may contain:

000823

The resulting cell will be numeric containing:

823

I know the trick when manually entering a value in a cell that is numeric
but you want treated as a string. Preceed the value with a single quote.

For example, manually entering the cell value:

'000823

The resulting cell will be text containing:

000823

But ...

Doing so in a CSV causes Excel to use that preceeding single quote as part
of the (now definitely non-numeric) string.

Is there a way (or another easily generated file format) to get these
seemingly numeric values to be treated as a string by Excel without
resorting to user intervention (such as the process of importing a text file
where the user is prompted for column placements, delimiters and value
type)?

Hello

I'm writing a macro that uses Form TextBoxes to handle unitarian prices with decimal point. My problem surfaces when the user has a Excel version in Spanish that uses the comma "," as decimal sepparator instead. Then unitarian prices become a huge number. 1.978 converts to 1,978.000

Any idea on how to solve this?

Thanks again

jose luis


No luck finding an answer? You could always try Google.