Thousands Separator

May 1, 2008

On my indows XP box, when I type a number into an Excel spreadsheet and it is in "General" format, the numbers appears like this (examples):

1234567

or this:

7.125

or this:

8.1

I want them to look just like that except for the first one, which I want to have the thousands separator:

1,234,567

I don't want to use a number format because
Excel makes you set a specific number of decimal places...I want the decimal places to float depending on how many decimal places there are in the entered number (just like the "General" format does). So basically, I want a "General" format, but with thousands separators (commas).

I tried using something like ###,###.### but that leaves a decimal place at the end of each integer (i.e. "123,456.") which is also not acceptable.

View 11 Replies


ADVERTISEMENT

VBA Number Formatting - Thousands Separator / No Decimals

Apr 11, 2014

How do I use a number formatting function that gives me the thousands seperator and no decimals?

View 1 Replies View Related

Text & Number Separator

Mar 19, 2009

I have following data in one coloumn & in each cell it contains both txt & number. i want to separate these text & number in separate cell.

01533FHGB06533FFHHHGJJ15445W153GJGJ0656HJHJ01533DFDFGB06554FFHHHGJJ15445W153GJGJ0656HJHJFLKFSH54646FGG56464DSHJDJD54346

View 9 Replies View Related

Text-to-column, Any Figure As Separator.

Oct 17, 2008

I have a column with a number of strings looking like below examples

View 3 Replies View Related

Thousand Separator As Indian Style

Jun 27, 2005

we in india to any number put comma as stated below

ie 15,13,10,565.00

fifteen crores thirteen lacs ten thousand five hundred sixty five only.

00,00,00,000.00

but excel we have only thousand separator.it separates the number after every thousand.

can it possible in excel to formate number as per our style.

00,00,00,000.00

View 5 Replies View Related

Decimal Separator Changes When Exporting To TXT File

Jul 1, 2011

I have made some macros for importing data from a txt fileand to sort the data into a sheet ready to be exported into another txt file. I have made VBA to work with "." as decimal separtor within the spreadsheet.

But, when I run the macro to export the data into a txt file (I have used the macros from this site [URL] .....) it automatically changes all "." into ","

But why?!?

I need the txt file to load data into another program, and this program need the use of "." as decimal separator - and not ",".

I'm using an European windows system.

View 5 Replies View Related

Data Which Needs To Be Formatted With Thousand Separator

Jan 20, 2012

I have data which needs to be formatted with a thousand separator as below.

400.00
1,000.00
250,000.00

How can I do this without it displaying as below

,400.00

I do not want it reflected in the case of hundreds.

View 3 Replies View Related

Join Email Addresses With Separator

Aug 13, 2008

I am looking to semi automate and mass emailing routine. I done code to validate the email now I just want to do some code to append the validated emails. Was hoping someone could offer a quick and easy way to accomplish this task. Below are a list of emails as they would be in excel below that is a variable “strRecipients” that is used to append all emails.

jey.boks@some.state.tx.us
Breay@iglobal.net
rccep@chiter.net
edd.champ@moeaent.com

strRecipients = strRecipients & ";" & ""jey.boks@some.state.tx.us""
strRecipients = strRecipients & ";" & "Breay@iglobal.net"
strRecipients = strRecipients & ";" & "rccep@chiter.net"
strRecipients = strRecipients & ";" & "edd.champ@moeaent.com"

View 3 Replies View Related

Text Function - Variable Decimal Separator

Sep 13, 2013

I have a workbook that is being used by users with both "," and "." as decimal separator. This is working all fine, except for one cell, where I need the number output to be displayed with two decimals. See formula below:

="Open at " & TEXT(+OHL!J$13+OHL!H$15,"0.00") & " and Go"

This example does not work with "," decimal settings.

Is there any different way to force two decimals in a text string, except for TEXT (where the format given in itself limits the cross decimal separator functionality)?

View 3 Replies View Related

How Can I Show Whole Thousands?

May 4, 2009

One of my colleagues wants Excel only to show whole thousands, but I am not sure how to do this

For example, if the number is 78654.98, he only wants it to show 78.

Is this possible?

View 5 Replies View Related

Saving Rows To Text File With Cell Separator?

Jul 16, 2014

I am trying to find a code that will allow me to Save Rows to a txt file with cell separator ?

I have put together this from other peoples work online and now all i need to work out is how to add a cell separator like a comer?

View 4 Replies View Related

Display Thousand Separator In Text Function Output?

Apr 24, 2013

I have numbers in cell A1 (say 3545852) as a sum of a data range. I want to display it like this "Rs. 35,45,852.00 Cr."

How can i do this. I used text function like this

="Rs. " & text(A1,"0,00.00") & " Cr."

But it displays "Rs. 3545852.00 Cr."

I want thousand separator also.

View 7 Replies View Related

Show 1000 Number Typed As 1,000 (comma Separator)

Mar 4, 2007

HOw do we hard code excel to show when number typed as with the 1000 separator.
For Eg: 1,000

View 9 Replies View Related

Replace Dots In Dates With Valid Date Separator

Feb 19, 2009

I have a SAP application that outputs a field of data from a specific query. One column, the date, is produced in the format 18.02.2009. Is there any excel 'trickery' that can convert this into a standard british date format i.e. 18/02/2009 & then allow me to sort the entire data field by ascending date order?

View 6 Replies View Related

Lakh Separator. Indian Rupee Cell Format

May 23, 2007

to format cells with "lakh" separator.

Excel allows only thousand's separator.

The other option is to make the changes in the regional settings... which will
affect all other application.

View 9 Replies View Related

VLookup In VBA With Thousands Of Rows

Nov 26, 2012

I am writing some code for a fast vlookup. I have the following template. The formulas in column AU and AX need to be automated. actual file has 170000 rows, so the vba code needs to be fast.

AU formula is: =VLOOKUP(A2,$BA$2:$BC$180000,3,FALSE)
AX formula is: = IF(AN2 = "Test1","To be adjusted",
IF(OR(AN2="Test2",AN2="Test3"),VLOOKUP(A2,$BE$2:$BF$180000,2,FALSE),
VLOOKUP(A2,$BA$2:$BC$180000,2,FALSE)))

View 9 Replies View Related

Excel Petty Cash - Department And Nominal Code Separator?

May 22, 2014

I have a petty cash sheet set up:

A1 - Date
B1 - Receipt No.
C1 - DEPT CODE
D1 - NOMINAL CODE
E1 - Reason
F1 - Amount
G1 - Running total (I've put a little formula in there to keep the balance - clever eh!)

So - this is fine - except I have to add the total amount for each department/nominal code each month.

What I'm doing at the moment is copying and pasting, sorting the sheet by:

Department
Nominal Code
Date

Then doing a manual add up of each and adding it to my month end report.

I'm really not an expert at all - but I thought there must be an easier way for me to do it. After inputting the petty cash there MUST be a way that it can automatically calculate it?

Or is this going to be a difficult thing to do requiring expertise in pivot tables etc?

View 2 Replies View Related

Delete Thousands Of Extra Columns

Dec 4, 2012

My production spreadsheet has thousands of unwanted columns and I am unsure as to how they appeared. I want to delete them. I have tried selecting all of them and deleting them, but nothing happens. I have tried go to > blanks, but it freezes because there are so many columns.

Also, I tried to copy paste to a new sheet, but the formulas get messed up.

For a mental picture, the spreadsheet is approx 225 rows by 25 columns.

View 14 Replies View Related

Transform Thousands Of Columns To Single Row?

Jan 7, 2014

how do I transform thousands of groups of columns (each group has 6 columns) into A Single row (placed into another sheet) using Macro?

Since my data is large, so efficiency is also important.

View 5 Replies View Related

Time Mask For Thousands Of Hours

Feb 13, 2009

Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45.

I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this:

View 6 Replies View Related

Format Currency In Thousands,lakhs

Apr 27, 2009

i want to know how to numberformat numbers (Currency) into thousands,lakhs.
For example i m having amount in colmun D like 1239.00 i want to format the numbers in this column as Currency with thousand seperator and lakh seperator. like1,239.00 or 1,23,900.0

View 2 Replies View Related

Macro - Use Spaces To Separate Thousands

Nov 26, 2012

In our language the thousand groups between numbers are separated with a space, like 2 345 345. (so we do not use , or . ) How can I do it in a macro, is there any possibility?

How should I change this: MERGEFIELD brutto # ### # ## ### Ft.

(I know this is without braces, I cant copy them). so this gives me a result 2345678 Ft. and I want 2 345 678 Ft.

View 7 Replies View Related

Thousands Of Cells Added To End After Formula Run

Dec 17, 2013

I'm using the record macro function to make a code that is concerned with formatting information generated in excel by another software program.

My original idea was to replace the information in column F with the concatenated info of column F-L, but I couldn't get this to work either. So now I'm looking for a work around for my work around.

I can get the macro to work just fine with "Macro 1" below, but unfortunately when I use the entire column range, it populates thousands and thousands of rows below the (around 50) lines of information I have. However, the populated information in my sheet is completely variable - it could be 1 line or 5000 lines. These will also be printed, so I cannot just cap the range at 5000 because then it would print all of the blanks.

I tried several work-arounds after googling. I tried to leave the blanks and then delete them once they were populated - messy, and it crashed excel. I tried End(xlUp) and I tried using lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row

how to only copy the formula in Column M to the last row of information (which would be the row with the last value in Column A).

MACRO 1:
Columns("M:M").Select
Selection.FormulaR1C1 = _
"=CONCATENATE(RC6, "" "", RC7, "" "", RC8, "" "", RC9, "" "", RC10, "" "", RC11, "" "", RC12)"
Range("M:M").End(xlDown).Select
Columns("M:M").Select
Selection.Copy
Columns("F:F").Select

[code].....

MACRO 2 (Only the Changes):

ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Select
Range("M" & lngLastRow).Select
Selection.FormulaR1C1 = _
"=CONCATENATE(RC6, "" "", RC7, "" "", RC8, "" "", RC9, "" "", RC10, "" "", RC11, "" "", RC12)"
Range("M" & lngLastRow).Select
Selection.Copy

View 4 Replies View Related

Convert Thousands Of Files: .csv To .xls Macro

Oct 5, 2004

I need to convert thousands of files from .csv to .xls format. Filenames are numbers (1.csv ... 20000.csv). Filenames do not change.

Could just open, Save As, and Close.

Is there a simple way to run a macro once and convert everything? I don't know how to code the macro to open one file, convert, then close, then open the next...

View 9 Replies View Related

Format TextBox With Thousands Separators

Nov 5, 2006

We have created a userform with several textbox for numbers. We'd like to show them with a format similar to "###,##0.00" and allow the users to enter numbers without commas, but always show the textbox formated. Is there any special way to manage this format? As the textbox is a "text" value, how can we manage the input and the numbers? Should we always replace the "," withi a nullstring? Example:

Private Sub tbCakPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
tbCakPrice.Text = Format(Val(tbCakPrice.Value), "######0.00")
tbPxCakCnFCgl.Value = Format(Val(tbCakPrice.Value) + Val(tbFleteRealCgl.Value), "########0.00")
End Sub.......................

View 8 Replies View Related

How To Delete Tens Of Thousands Of Empty Cells

Sep 3, 2013

I am working with a worksheet that has has 26 columns and about 2000 rows. However, somehow Excel has added tens of thousands of rows and columns to it, all of them empty. I know this is causing some memory trouble but I can't find an easy way to get rid of them. At one point I fixed the problem by selecting the cells I was using and starting a new worksheet, but I don't want to have to do that every day.

View 4 Replies View Related

Convert Numbers To Thousands Without Physically Changing Each One?

Apr 18, 2014

To convert numbers (975,095.97) to thousands (975.1) without physcially changing each one?

View 2 Replies View Related

Easy Weighted Average For Thousands Of Items

May 29, 2008

I am trying to calculate weighted average cost for a list of thousands of items.

I will try to explain this as best I can.

Column A is item. Column B is Mode. Column C is Volume. Column D is Cost

There are only 2 Transmode Per Item. So each item is pretty much listed twice and I need to calculate the weighted average cost per item.

I know how to use the SUMPRODUCT function to calculate weighted average and could slowly go through the spreadsheet item by item calculating this, but is there a way to do this with some sort of formula? Similar to a Subtotal that would recognize each change item and calculate the Weighted Average at that change.

View 9 Replies View Related

Millions & Thousands Custom Number Formatting

Feb 24, 2009

To display 1,100,000 as $1.1M & 110,000 as $110.0K I've used the below Custom Number Formatting.

[>=1000000] $#,##0.0,,"M";[

View 9 Replies View Related

Excel 2010 :: Cell Selection - Set Comma To Appear For All Thousands

Apr 12, 2014

Question 1: how i can automatically add a cell into a formula by just clicking the cell to be added? in other words, say i typed an "=" sign in a cell and want to add a number of cells just by clicking the mouse without having to type the "+" sign after selecting each cell. the formula will just appear as > =A1+B1+C1 by default with each click of the mouse. Is there a default setting that can be set so the plus sign will appear with each click?

Question 2: how can i set a comma to appear for all thousands, (i.e. 1,000) without having to format each cell individually to show commas?? in other words that any spreadsheet i open will always insert comas for numbers larger by a thousand?

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved