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.
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
for the number inserted in A1 if I pressed comma button it give me 1,000,000 as in the regional setting group of 3 in given. My question is when I press comma button for the inserted value i.e. 1000000 it should give me 10,00,000. First group is of 3 digit and after that all gourps are of 2 digit
Value Excel Gives Result I required this kind of formatting
1000000 1,000,000 10,00,000
2023154568 2,023,154,568 2,02,31,54,568
Pl reply on (email address deleted by moderator)
Will some one guide me, How can i covert my currency (Rupee) in to Million?
(2700000 Rs Into Million with formula/automatically)
Secondly,
Can i make in a specific cell 4 colums & 2 rows ? Is it possible. ?
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?
Specially in accounting jobs we need to convert numbers into text when expressing currency values. writing a macro everytime and running it is cumbersome. isnt there an add on?
View 1 Replies View RelatedIs it possible to split a cell into 2 different cells, using the last "" (backslash) in the cell as the separator? ...
View 9 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 following data in one coloumn & in each cell it contains both txt & number. i want to separate these text & number in separate cell.
01533FHGB06533FFHHHGJJ15445W153GJGJ0656HJHJ01533DFDFGB06554FFHHHGJJ15445W153GJGJ0656HJHJFLKFSH54646FGG56464DSHJDJD54346
I 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 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.
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 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.
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 RelatedI 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 would like to have a single button that changes a range of cells from the USD to EURO to perhaps CAD symbol. Can this operation be performed, such that if I start in dollars, and I click the button once, it shifts by range to EURO (not formulas...just symbol)...and then if I click the same button again, it goes to CAD, and then back to USD with a third click?
View 5 Replies View RelatedI need in changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.
I have a column in excel which gets populated in the format "2/1/1980" when copied from a different source. I want a macro to run on this column and convert this in the below format. Required format: '02-01-80 (note the ' symbol before 02 which will keep it as text)
Existing ----> Required
2/1/1980 --> '02-01-80
2/1/1970 --> '02-01-70
2/1/1983 --> '02-01-83
I need this macro to run on two columns and replace them with the required format. I can do a find and replace either in the sheet or through hard coding the VBA, but as the date format keeps growing, I would need to do this via code and hence requesting the same.
I have data in cell A1 that often changes its number format(from $ to %), can i have the number format change in cell B1 whenever the number format in cell A1 changes?
View 3 Replies View RelatedBeen racking brain, searching through the forum here, and my Excel 2003 Bible all day trying to figure out this problem to no avail. I would like to clear the contents of any cell in a given range if the cell immediately to the right of is formatted as bold.
View 2 Replies View RelatedI have a spread sheet and I want to conditionally format rows to be a certain color. That part I'm fine with. But I don't want them to be a set color. I have a "key" of different colored cells that I want to be the fill colors of the formatting. The ultimate goal is that for example the key looked like this
red
blue
yellow
green
then the rows I had would be formatted as red, blue, yellow, and green. But if you were to go into the key and change the first cell from red to purple, then the rows would become formatted as purple, blue, yellow, and green. Obviously I can copy formating by hand using the format painter, but I want it to update automatically.
Below is my code which isn't formatting the cells it's suppossed to. It looks like it isn't doing anything. I think the issue might be with the highlighted section of my code, but when I go to "Manage" my rules for conditional formatting, excel references the appropriate cell under the "Applies to" section. I am using relative cell references for for the majority of the rest of the code and this section follows a section that selects the correct cell for this conditional format.
Code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=""ABS()>.005"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority[code].....
Is it possible to change cell format from custom to general or text and keep the cell value
I have this in formula bar: 16/08/2013 4:37:00 AM
which is displayed in the cell as: 16/08/2013 4:37
I want to change the cell format to general but display the same information.
let's say cell A1 is colored blue manually, how can i format cell b1 to be colored red? is it possible in conditional formatting?
View 7 Replies View RelatedCell C3 has "Joe"
Cell X44 is a VLOOKUP that retrieves "Joe"
Let's say Joe goes on vacation. The workbook user goes in and puts a blue fill in C3. Cell X44 would also need to change automatically.
What's needed to make this happen?
I have the following data on a spreadsheet created by a formula:
A1: 1.1234
A2. 2.234
A3: 3.34
I have the data formatted to only have two decimal places so it looks like this:
A1: 1.12
A2: 2.23
A3: 3.34
I'm using the following formula in cell B1 to display the values of those cells in a single cell in order A1,A2,A3:
=A1 & "," & A2 & "," & A3
However when the values of A1, A2 and A3 appear in the in cell B1 they appear as: 1.1234,2.234,3.34
Is there a way I can edit the formula or format something so the values in B1 appear as with only two decimal places? i.e 1.12,2.23,3.34