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

Free Microsoft Excel 2013 Quick Reference

Number format to replace decimal with comma Results

Hi there

I have some numbers that I reformat from eg 1,000,000.00 into 1.000.000,00 or 100.00 into 100,00

now I need to take the thousand separator from the resulting only changing the . into a ,


	VB:
	
 'with a comma.
Private Function ReformattedNumber(ByVal Num As Double) As String 
     
    If InStr(1, Num, ".") > 0 Then 
         
         
        ReformattedNumber = Replace(Num, ".", ",") 
         
         
         
    Else 
         
        ReformattedNumber = Num & "," 
         
    End If 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Auto Merged Post Until 24 Hrs Passes;

sorry being stupid & overcomplicating it.. all it needs it seems is>

	VB:
	
ReformattedNumber = Num & "," 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hi everyone,

I am having problems applying custom format to numbers that need to be formatted.

custom format use is 0",00"

When I apply the custom format above to whole numbers, I achieve the results I want:

0.00
20.00
40.00

becomes

0,00
20,00
40,00

However, when I apply to numbers with decimal places

0.20
0.40
0.60

becomes

0,00
0,00
1,00

anyone knows how I can change the custom format so that the period is replaced with a comma but the decimal values are preserved?

appreciate your help, thanks!

I have a ComboBox list in a UserForm containing the numbers:

0.050
0.100
0.150
1.000
1.500
2.000

When I switch the WinXP Region Language to European (i'm in the US), I
have successfully declared the formatting to keep the 3 decimals and
replace the decimals with comma's so the list looks like this:

0,050
0,100
0,150
1,000
1,500
2,000

However, when I select a value from the list, it does not store in the
Excel Spreadsheet Cell as a number, but as a string of text. So, for
example, "2,000" would read "2000" in European Language formatting.

Does anybody know of a way to retain the numeric formatting and let it
carry from the ComboBox list in my UserForm to the ActiveCell?

Thanks!!!!

I've created a small macro to replace a dot with a comma sign, but it doesn't work in all the cells I want to make the replacement. See attached file.

When pressing the button, the macro is supposed to replace the dot to a comma sign in the selected cell. In cells A3 and A4 it works, but in cells A1 and A2 the comma sign is placed after the decimals. I've tried to switch cell format between General and Number and also the number of decimals when using the Number format, but can't see any difference.

MS Excel does not recognise the Indian Number Format of Lacs (= 100,000) and
Crores (=10 million). That is, the comma is put after the 5th place left from
the decimal point for 'Lacs' and after the7th place left from the decimal
point.

I have an Indian Accounting Software called 'Ex' developed by TCS. It
exports data in Indian Number Formats as above. Unfortunately, I can not
process this data as Excel does not recognise this format. This format is
very extensively used in Indian Sub-continent. In order to process this data,
I have remove all the commas by 'find and replace' method. If Excel can have
any feature in Number Format called 'Lacs and Crores' then this data can be
directly precesable.

For me and million (i.e. lacs and crores) others, this is very very
important function.
--
Shrikant

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...lic.excel.misc

I configured excel 2007 to download data from the web. But the numbers are like this: 143,00 instead of 143 (example). So with these numbers are not considered as numbers and the calculations involving these have no value. The page I download has the format I mention above but in Excel 2010 I occasionally I use it is recognized and converted to 143 automatically. (again example). But the Excel 2007 I always use do not have this possibility.I would not upgrade to Excel 2010 just for this purpose - everything else works fine for me. One more thing to clarify - I use the Turkish format and in Turkish, the numbers thousands separator and decimal seperator are opposite (comma instead of dot and dot instead of comma) and foreign site downloaded web sites numbers can not be recognized. So is there any way to change the symbols to our desired format automatically after the page is downloaded every 2 minutes? And as an addition, In example some sites use some symbols such as 143.00- and the "-" symbols makes the number loose its number attribute thus no calculations can be made. I know "find and replace" but I want it to be automated each time the data refreshes.

Thanks for your input!.

ps. regarding the Turkish comma format I know control panel settings -- but it changes the whole system and all excel files. And some data downloaded has Turkish number format and some has US format so making a general change wont work.

Hello!

I have a table from which I get times, for example 1:43.00 (with a dot as a decimal separator)
Then I want to load this time into another worksheet and do calculations with it. However, Excel2003 does not recognize it as a time.
In Excel2007 it works fine but I need to make it work in Excel2003 as well.

I've tried replacing the dots with commas by a macro but the cells don't change into times until I doubleclick the cell and hit enter.
If I record a macro to see what happens when I doubleclick the cell I get:
All my cells are formated as mm:ss,00

Can anyone shed some light on this?

I find it very mysterious and after few days of playing around I thought I will post it here and hopefully somebody came accross similar behaviour and have a solution for this. I am in a final stage of my little project and this behaviour is really killing my confidence...

I have data source in csv format. There are fields containing numbers, where decimals are separated by ".". My Control Panel setting is "," for decimal separation and I was not willing to change it. I was rather searching ways to overcome by instructing Excel in some way to understand it as a number. If I just do plain Data -> Import External Data, this give my half numbers half text format... Numbers look like numbers, with comma as delimiter, but those, that had decimals are aligned to the left. I can do math with a single cell that contains this hybrid and formula gives me result in usual number format. However if I enter some formula for multiple cells (like SUM) then it fails to add all these hybrid numbers, adds only those without decimals, that are aligned to right.

If this would the the only source file, I could probably locate the field that contain number and replace "." with ",". However I have multiple source files and numbers are in various positions in those files. I also want to be able to add more source files in the future and be able to use it without getting into VBA. In that rescpect, this REPLACE method was not most attractive.

I have then came accross Source Definition for CSV and TXT files with schema.ini file containing detailed definition of the file. This gave me hope. I defined "." as decimal delimiter (DecimalSymbol=. ) and did Data Import using predefined Data Source. It looked perfect. All the numbers where interpreted correctly, I can apply formulas on them, no problems. I have then tried to move the field to the position where I want it to be (Since the field name is Amount, it was placed in first column and this was not the place where I wanted it). So I insert one column futher to the right, move Amount column over there. So far so good. Refreshing data brings me my numbers as numbers. I have then deleted first column, as it was empty. All my numbers turned into nasty hybrids

I don't really understand what causes this bahaviour, but certainly do not want to leave the file for other users to use, when it behaves as it does.

Would really appreciate some advice.

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 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)?

Hello!

My problem goes like this:
I use the following code to import data from .txt file

	VB:
	
Workbooks.OpenText Filename:=originalPath & "" & "ECDISP.OUT", Origin:= _ 
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _ 
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _ 
Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _ 
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The ECDISP.OUT file (and concequently, the ECDISP worksheet) contains numbers (mostly double) as the result of a dispatch problem. The decimal seperator of the program is ".", so the results contain numbers of the form XX.X and XX.XXX (with one and two decimal digits respectively).
I use the greek version of Office, where the decimal seperator is "," so, in order to present the results to the users of this program and interface I replace the "." produced by the program with the "," as shown bellow:

	VB:
	
 
    Worksheets("Öýëëď2").Columns("D").Replace _ 
    What:=".", Replacement:=",", _ 
    SearchOrder:=xlByColumns, MatchCase:=True 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Everything works fine for the numbers of the form XX.X (they are converted to XX,X) but this doesn't happen for these of the form XX.XXX (they remain the same).
Let me explain the reason for that:
when the data are imported in ECDISP worksheet the numbers of the form XX.X are formatted as General, but the numbers of the other form (XX.XXX) are formatted as Number and the option "Use 1000 Separator (.)" is ticked. When I try to convert it to General the outcome is XXXXX; the same goes when I untick the option for the 1000 separator.

Any ideas? Sorry for the long post!
Thank you in advance for time and suggestions!

Athina

Hi everyone, I have some questions regarding Sub-Totals:

I have a macro to insert sub-totals on a spreadsheet with multiple columns.
I want to Sum the values in most columns, Count the values in Column 7, and
Average the values in Columns 9, 16, and 18. My code looks like this:

Selection.Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(10, 11,
12 _
, 13, 14, 15, 17, 19, 20), Replace:=True, PageBreaks:=False,
SummaryBelowData:=True
Selection.Subtotal GroupBy:=3, Function:=xlCount, TotalList:=Array(7), _
Replace:=False, PageBreaks:=False, SummaryBelowData:=True
Selection.Subtotal GroupBy:=3, Function:=xlAverage, TotalList:=Array(9,
16 _
, 18), Replace:=False, PageBreaks:=False, SummaryBelowData:= True

However, I would like to show the Subtotal Sums, Counts, and Averages on the
same SubTotal row, not on separate rows. How can I do this? The workaround
I am currently using is to Sum all columns first, then select Column 7 and do
a search and replace on the subtotal Sum formula to change it to Count, then
use the same process to change Columns 9, 16, and 18 to Average. This seems
cumbersome, is there a cleaner way to do this?

2 follow-up questions:

1) Can I format the entire row with the Subtotals to be in bold font, as
opposed to just the row heading “Column 3 Total” being in bold?

2) Can I format the Count Sub-Total values for Column 7 to be in number
format with commas and no decimal places? The data in Column 7 is in a
Custom Format with leading zeroes, but I do not want the Count Sub-Totals to
be in that format.

Dear clever folks!

I'm living in Sweden where we use komma instead of period as seperator
for the decimal part in numbers.
This is no good since all (almost) programes uses period as a
seperator.
I'm doing some number manipulations in Excel and at the end I'm
suppose to export my data as a text file, but Excel exports it with a
komma instead of period, which my next programe cannot interpret.
I know that changing my regional settings within Windows solves this
problem, but I don't have administrative privilgies on the computer so
that is of no good.

I have done a basic macro that looks like this:

Sub ConvertCommaToPeriod()

Worksheets(2).Columns("B").Replace _
What:=",", Replacement:=".", LookAt:=xlPart ', _
'SearchOrder:=xlByColumns ', MatchCase:=True

End Sub

This works well for data that looks like this:

'1234,5678 (notice the apostrophe in the beginning!)

But fails to recognise

1234,5678 (cell formated as text)

What is up with this?

I could of course insert an apostrophe in the beginning of every cell,
but surely there must be a better way?

Best regards
Freddie


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