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
ADVERTISEMENT
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
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
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
Oct 17, 2008
I have a column with a number of strings looking like below examples
View 3 Replies
View Related
Apr 22, 2006
I am using the following code.
Function VLOOKUPRow()
Dim SV As Object
Worksheets("Midterm").Select
SV = "UserFormAppend.TextBoxInvID.Text"
VRow = Columns(1). Find(What:="SV", After:= Cells(1, 1), _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True).Row
MsgBox (VRow)
End Function
I am trying to give the VRow variable the value or the row that The text from my text box is on. How do you make the what look for a variable. I can get it to work if i put a constent in the what. how to make the what look for a variable. I'm new at this forum thing so im not quite sure how to check for answers.
View 2 Replies
View Related
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
Feb 21, 2009
Cell C9: 1.25773
Cell C10: 20.0
Cell C11: 2.25%
Cell C15: =C9+((C10*C11)/10000)
C9 is a user entered value, currently formated general
C10 is a user entered value, currently formated decimal with one decimal
C11 is a user entered percentage, currently formated percent with two decimals
C15 is a formula calculation, currently formated general
I've tried formating cells to general (and/or) text and the values appear to be correct but still don't show what I want.
The most popular entries for C9 will be:
123.12
123.123
1.1234
1.12345
Any of the above could have one or more trailing zeros.
I would like C15 to show the same amount of decimal places that the user enters in C9. If user enters one, two, three, four, five, etc...decimal places in C9 then show the same amount of decimal places in C15 after the calculation is done and include any trailing 0's that are needed to match the number of decimals in C9.
I've tried different If statements with custom format to try to get the format of C9 transfered to C15 but haven't come across the right way to do this.
View 14 Replies
View Related
May 2, 2008
Using excel 2003
What is the proper declaration for a decimal, I’ve been using currency because it has two decimal points i.e.
Dim x as Currency ...
View 9 Replies
View Related
Sep 28, 2007
I have an If loop which looks like: If x < 54.5 And x > 52 Then
I have declared x as "Long", but still when it does the comparison it seems to round x up to the nearest whole number for the second comparison ( x > 52 ).
View 4 Replies
View Related
Jun 3, 2008
Why when I want to use a varaible with a value like that 2.1, 0.9, 3.5 in a code to create a formula gives me an error? How to get it work?
Sub Code1()
Dim k As Double
k = 2.1
Range("h11").Formula = "=" & k
End Sub
Strange, but it works well if k is an integer with no Decimal Fraction, like 1, 2, 5, 11 ..
View 4 Replies
View Related
Jul 2, 2013
I have a list with a variety of numbers:
a) some with large units (never more than 10 units long) and possibly also decimals,
b) others with minimal units but long decimal characters,
c) and others containing both.
I need a formula that can be applied to each individual number, that removes a variable amount of decimal places, resulting in a maximum character length of 14 (i.e. sometimes removing a few decimal places, other times removing a substantial amount of decimal places). Removed decimals are displayed in red in the following example
a) 1234567890.123456789
b) 1.2345678901234567
c) 123456.789012345678901234567890
View 6 Replies
View Related
May 14, 2014
Here's what I'm doing: I'm using a macro to assign a cell value to a variable then set another cell value to the variable instead of copy/paste (because even pasting values only was affecting other formulas in the file for some reason)
The problem: in using the macro, the number being 'copied' is acquiring a few extra decimal places IE 38334.61 is the original number and 38334.609375 is what I end up with. The numbers come from a CSV with only 2 decimal places and I checked by adding decimal places in the format so it's not a formatting/visible digits issue. Since the values are hour meter readings, the extra decimal places end up with very small values outside 0-24 hrs which messes with sorting and usage %. The values are so small all of them together in a month add up to a fraction of a cent but it's one of those things that bugs my OCD by not being right.
My VBA book explains the min/max capabilities, content type, memory bits but not fiddly details like this.
So my question is this: right now I'm declaring the clipboard-substitue variable as an single, is there a different one that would work better without adding anything? Criteria are: numeric, 6 digits before the decimal, 2 after, all positive values. (Ie 123456.12)
EDIT: fixed my senior moment.
View 6 Replies
View Related
Feb 26, 2009
I need to create a formula in a spreadsheet so that when KWD or BHD is entered into a cell then another cell changes to 8 decimal places if neither of these are in the box then it needs to be 7 decimals! But the box that is changing needs to be alterable.
I have posted this on a non excel specialised forums and i got this answer:
in cell x you have "kwd"
in cell y you put: =IF(cell X="kwd",TEXT(cell Z,"#,##0.00000000"),TEXT(CELL z,"#,##0.0000000"))
in cell Z you put the value that you want to be alterable
View 14 Replies
View Related
Feb 11, 2013
I am using this formula to output data into a cell:
=MID(A1,4,2)
The cell value is 50. I am wanting it to appear as 5.0 instead. Can I do this by adding to the formula above or will I need to utilize another cell to accomplish this?
View 2 Replies
View Related
Mar 27, 2007
I'm trying to do is to convert an hours per week field as a decimal into hh:mm as text. For example, 22.8 would convert to 22:48.
Public Function DecToHours(Ldecimal1 As Long)
Dim Ldecimal2 As Long
Dim Shours As String
Ldecimal1 = ActiveCell - Round(ActiveCell, 0)
Ldecimal2 = (ActiveCell - Ldecimal1) * 60
Shours = Round(ActiveCell, 0) & ":" & Ldecimal2
End Function
View 5 Replies
View Related
Mar 13, 2014
I'm on Excel 2010 and I need to round up a complete price list to a 0.90 decimal.
Here is the formula that I have in my cell : =((J5-(J5*N$2))*M5) I need to round the result to XXX.90
View 4 Replies
View Related
Dec 27, 2011
I need a function to find the number of decimal places of a certain number (in this specific case doubles)
The first solution would be something like this:
Code:
'returns the number of decimal places within a double
Public Function getDecPlaces(inputNum As Double) As Long
Dim ndx As Long
ndx = InStr(1, inputNum, ".")
If ndx > 0 Then
getDecPlaces = Len$(CStr(inputNum)) - ndx
End If
End Function
But i feel there is likely a much better way of doing this..
View 9 Replies
View Related
Dec 4, 2007
I have created a userdefined function. the Idea is for the user to calculate the difference in hours and minutes. The input would be like budtime = 30 , realtime = 1.25 the answer I would like to have is 28:45 (28hours and 45 mins). Im aware that I can format the cell manually with [h]:mm but I'd like the function to do it.
I have written this:
Function RT(BudTime, RealTime)
RT = Format(((CDec(BudTime) - CDec(RealTime) / 24)), "[h]:mm")
End Function
View 5 Replies
View Related
Feb 17, 2010
Any function in Excel, that would allow me to have a unit cost price (for example: 0.5432) and then for a table of figures containing the pack sizes to determine the correct unit price that would allow all pack costs to be at 2 decimal places. I have included an example below:
My spreadsheet would look like the following:
Cell A1 (Unit Cost Price) = .5814
Cell A3 (Pack of 75 units) = .5814*75 = 43.605
Cell A4 (Pack of 80 units) = .5814*80 = 46.512
Cell A5 (Pack of 100 units) = .5814*100 = 58.14
and so on
I need some way of making cell A1 change to a value that will cause all cells A3 to A5 to be 2 decimal places or less.
View 3 Replies
View Related
Feb 5, 2014
I have a small issues with number formatting
I need to combine text and a number
- Ex Water - 13
The issues that i have is that when i do this excel adds decimals - Ex Water - 13,2
How do i avoid this using number formatting?
View 2 Replies
View Related
Sep 2, 2009
I got following Hex data in text format, I need change them to decimal,
0324 00BF 0000 0000 030C 00C6 0000 0000
0305 00C7 0000 0000 02FE 300F 040B 4753
4D4D 5352 4E4D 5343 4E31 0030 0F0A 0101
firstly I did txt to columns, then I found some data changed to scientific format which is not I wanted as I could not convert it using function HEX2DEC.
032400BF00000000030C00C600000000
030500C70000000002FE300F040B4753
4D4D53524E4D53434.00E+3100300F0A0101
if I set column data format to text when I doing Text to Columns operation then all data will be text, so I still can not convert them to decimal.
View 4 Replies
View Related
Nov 6, 2006
A while back I asked how to remove the letters from entries that have both numbers and letters. I as pointed to a UDF called "ExtractNumber".
Here is the code for that UDF: .....
View 9 Replies
View Related
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
View Related
May 10, 2014
AVERAGEIFS(X$2:X$1000,$AT$2:$AT$1000,1)& TEXT(BE2/BD2," 0%")
The formula is working , but the result is : 3.50239234449761 58% Formatting the cell did not work. Can these decimals be slashed?
View 5 Replies
View Related
Aug 14, 2007
i wanna convert the decimal number to a round number...like 2.0L to 2L.
View 6 Replies
View Related
Aug 13, 2009
I have a vba macro that takes data from one workbook and pastes it into another workbook. In doing this I have declared a few variables of type single (I only need two decimal precision). However, when I copy the values from the cells on the source workbook and paste them into the target workbook, the numbers end up having 12 decimal places. Ultimately, this extra precision causes my totals to be off by .01 or more after a while. I have tried rounding the number as I pull it off the source workbook into the variable, but that didn't matter. How do I solve this problem? Code for pulling data from source workbook:...
View 2 Replies
View Related
Aug 3, 2006
Basically, I want to format a group of cells to display 1 decimal figure if the number is not a whole number. If the number is a whole number (or if the rounded first decimal place is 0) I want it to display no decimal.
View 9 Replies
View Related
Jun 4, 2008
I am wondering if there is any way to "force" a number to have three decimal places instead of two and still have that number valued as a number for use in formulas later (like AVERAGE, etc.).
What I have is a combination of HLOOKUPs, IF statements, and Conditional Formatting.
I have a Master Grades sheet and individual Class sheets. I use an HLOOKUP, matching a Student Number in both sheets, and looking in the Class sheet for the P/F indicator. If the indicator is a F, the HLOOKUP is to return the Final Grade from the Class sheet and input that into the Master Grades sheet, forced to three decimal places (whereas the numbers in the Class sheets are at two decimal places) If the indicator is P, the HLOOKUP simply returns the Final Grade from the Class sheet and inputs it into the Master Grades sheet with two decimal places.
I have the failure grades forced to three decimal places so that the Conditional Formatting in the Master Grades sheet highlights the failed grades for teachers and anyone else can see right away that a student failed, but the number still allows the Master Grades sheet to calculate the overall AVERAGE for the student as well as the AVERAGE for that class.
If any of this is not quite clear, please ask and I can further expand on what I am doing.
Here is the code I have right now that is not working correctly (and I know why it's not): ....
View 9 Replies
View Related
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
