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.
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
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.
The problem that I have is rolling over the sum total after the meter read reaches thousand it starts at 1 again, but I need the total KMs traveled for example in the car dash board the odometer shows 945 when I started my trip and end at 24, how would I set up a formula in excel to calculate the total KMs traveled
View 9 Replies View RelatedI am trying to do a mass import of several thousand excel files into one file. The headers of all the files are the same, but the data inside each file is different. I thought about using Access but it wants me to import each individual one.
I have zero experience in writing a Macro
I'm using a vlookup formula, and the thing I'm searching by is a part number. The problem is that in my table array, all the cells for part numbers have that green error flap in every cell, saying "The number in this cell is formatted as text or preceded by an apostrophe."
Some part numbers have letters on the end of them, ie: 12345N. When I go into a particular cell's error drop down menu and choose "Convert to Number," the vlookup will work with this cell. My problem is that the table array is several thousand rows, and I don't know how to fix them all at once.
I have a cell that calculates an etch time, eg 41.88. The cell is formatted to give me whole numbers and quarters only so that the operators input is made easier, in this case 42.
When I use a macro to copy this to another worksheet, although it appears as 42, the actual cell data is 41.88. Anyway I can get the actual cell data to be 42?
I have a graph that pulls data from a toggled list. Values include currency, percentages and general numbers. The data is set up as text to report as currency, percentages and general numbers. Is it possible to have a graph read these text values?
View 1 Replies View RelatedReceive worksheets with data in different layouts that needs to be moved into formatted worksheet with unique layout and field size to import in to Access database.
Example of formatted table layout ....
I ran a very simple code that conditionally formatted red any cell sum greater than 12. The macro worked all the way to the end - about 15k rows. After saving and re-opening file only the first 1603 lines had the formatting and the rest did not. Several attempts end with no change in results.
I did the programming in Office 97 but edited and ran it on Excel 2002.
I'm trying to lookup data in a cell formatted as time, the array is also formatted as time but I continue to get the #N/A. Is there a certain format that I should use?
View 9 Replies View RelatedI'm trying to lookup data in a cell formatted as time, the array is also formatted as time but I continue to get the #N/A. Is there a certain format that I should use
View 6 Replies View RelatedOn 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.
I have a data entry field on a form that is formatted for percentages. Sometimes when i key in "10" it will correctly convert it to "10%" but other times it randomly converts it to "1000%". When the error occurs, I'm able to correct it by re entering the value a few times until it displays correctly as "10%".
View 2 Replies View RelatedPart of my work involves the transfer of thousands of lines of Excel data into FileMaker and then exporting this data to Word where it is finally formatted for publication. What I'd ideally like is to skip the FileMaker step and simply have it so that I import all the data from my Excel file into a Word document in one swoop. The problem is that the eventual export can't feature tables, it would be the icing on the cake for the data to remain as formatted, and the data needs to read line<space>line<space>, vertically.
Essentially, I need to know how to take this from Excel ..
this1.jpg
And turn it into this in Word ...
this2result.jpg
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
Is it possible to split a cell into 2 different cells, using the last "" (backslash) in the cell as the separator? ...
View 9 Replies View RelatedI have a column with a number of strings looking like below examples
View 3 Replies View RelatedI 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.
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"
How do I use a number formatting function that gives me the thousands seperator and no decimals?
View 1 Replies View RelatedI 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)?
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?
HOw do we hard code excel to show when number typed as with the 1000 separator.
For Eg: 1,000
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 Relatedto 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.
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?
I have a strange issue. I have column "B" has date texts in it ( don't know if it means value) like 21.09.2012. The cells are in text format.
VB:
Sub ReFormatCell()
Range("B:B").Select
Selection.NumberFormat = "dd.mm.yyyy ddd"
With Range("B:B")
.Formula = .Value
End With
I tried ".Value = .Value" or ".Formula = .Value" or Copy paste or offset...... Non of the codes didn't work to make date format as I desired :/
The order is something like:
1. Format column "B" as text.
2. Offset text value 21.09.2012 to B.
3. Reformat cell as date
4......?!?!
What should I change to display "21.09.2012" as "21.09.2012 Wed" in B:B range?
I'm working with a line of code I can't seem to get right. I've got this string that will put breaks in, but I've realized it would make my life easier if the code, along with inserting a break, then inserted a formatted gray divider, it would make it easier. Here's what I have:
Sub BreakSections()
'This macro breaks the new data on sheets into their individual groups, inserting a blank line_
'which will need to be filled with a gray dividing line
Dim i As Long, j As Long
Application. ScreenUpdating = False
With Sheets("Contract Manufacturers")
. Cells.UnMerge
i = .UsedRange.Rows.Count
End With
For j = i To 2 Step -1
If Not IsEmpty(Cells(j, 5)) And Cells(j + 1, 5) <> Cells(j, 5) Then Rows(j + 1).Insert
Next
Application.ScreenUpdating = True
End Sub
I've tried then to put this code in after the Insert:
Range(ActiveCell, Range("AJ" & ActiveCell.Row)).Select
and then the formatting code.....
But I end up getting a whole mess of gray rows, displacing a lot of data. Is it possible to do these things together, or I should write another macro that just looks for the blank spots? It seems like it would be easier to do it all at once.
I have Excel 2008 for Mac (vers 12.0). On a basic spreadsheet, the first column is for date which I have formatted so I can type in 15/8/9 and it appears as 15-Aug-09.
However, each time I open Excel after having Quit it, I have to reformat the cells in this column again as they revert to converting my date input with something like 39,123.
My other columns are formatted to currency and are always okay.