Converting Comma To Decimal Point
I've just imported a file into a UK version of Excel from a German made datalogger. The decimal points (which should look like this 0.0) all appear as comas (0,0) with the result that Excel can't read them.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Convert Or Format Decimal To X Digits Without Decimal Point
I am trying to create a unique sample code by putting together the values of other cells that a user will input. It's all working well apart from the last part, where I am trying to include a decimal number. I want the decimal number to appear without the central "." and in a four digit format. e.g. 2.5 would appear as 0250, 14.25 would appear as 1425. This is the formlua I am using currently: =IF(ISBLANK(B4),"",IF(LEFT(C4,1)="w",(B4&"-"&TEXT(F4,"YYMMDD")&C4&TEXT(G4,"HHMM")),(B4&"-"&TEXT(F4,"YYMMDD")&C4&LEFT(TEXT(H4,"00"),2)&RIGHT(TEXT(H4,"00"),2)))) However, where the value of H4 is 2.5, I am getting a result of 0303 (I've put this part in bold). I have attached a small spreadsheet to aid understanding.
View Replies!
View Related
Cell Changes Decimal To Comma
I have a UserForm with different text boxes, everytime I put a number with decimals (ex 100.23) on a Textbox and I want that number to be shown on a excel cell, VBA changes the "dot" for a "comma" so excel understands it as an integer number 100,23 (10023).
View Replies!
View Related
Return Nth Value To Right Of Decimal Point
Would like to do the following for our school, In cell A1 there is a number, for instance: 12.12345 Based on that number, the 3rd number after the decimal is important. If the 3rd number after the decimal is “x” then the contents returned in b2 is a text value ( like high school ) .........
View Replies!
View Related
Check If The Number Have Two Decimal Point
a1 -> 2.333.33 "i want this to return as 2,333.33" a2 -> 33.33 "i want this to retun 33.33" a3 -> 45.555.55 "i want this to return 45,555.55" i already asked this question and someone gave me this code -> substitute(a1,".","",1) which return to 2333.33 but the problem is for the a2 if i use the code it will return as "3333" which read as "3,333". Is there any way that we can determine if the values have two decimal point before it will run the command?
View Replies!
View Related
Cells Inserting Decimal Point
This problem just appeared recently, as if out of the blue, and it seems to affect all of my old work sheets as well as new ones. I am using 2002 version. When I input any number in any cell the program automatically inserts a decimal point. Thus 1 becomes .1, 12 becomes 1.2, 123 becomes 12.3 etc. I have tried formatting the cells to "general" and to to "number" with no change. The thing that really gets me is that old saved worksheets exhibit this behavior as well. I can work around the problem by inputting 1. or 12. which then gives me the entry I am after but I've never needed to do this before.
View Replies!
View Related
Custom Format To Replace Decimal With Comma
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....... how I can change the custom format so that the period is replaced with a comma but the decimal values are preserved?
View Replies!
View Related
Number Format: Change Decimal To Comma
Is there any way to change the decimal point from the usual period symbol (.) to a comma (,). The reason being, i have a structural design spreadsheet and the new terminology is just so, changed from 00.00 to 00,00 I have tried cell formatting but it just uses a comma (,) for numbers in their thousand and it wont do what i need.
View Replies!
View Related
Force Decimal In Place Of Comma In TextBox
I have an UserForm, where I have several text boxes. One of these textboxes should be entered with decimals. I have been able to cope with the declarations, and set the variable as Variant. However, is it possible to prevent the user inserting "," instead of "."? Alternatively, automatically change "," to "."?
View Replies!
View Related
Make Any Number Begin With Decimal Point
I have a column that has values ranging anywhere from 1 to 999,999 and I need a way in vba to take whatever value is in the specified cell and place a decimal point at the far left. Example: change 175526.34 to .17552634 change 376.1 to .3761 change 22987.254 to .22978254 when the code is run I need the selected number to begin with the decimal point. See, the tricky part is that the length of the number varies and I cant just divide by X to shift so many places to the left..
View Replies!
View Related
Converting Units And Decimal Places.
I have a simple spreadsheet that allows the user to enter a dimension in metric or inches. I want to display the other units in the adjacent cell. In cell A1, the units are "Metric" or "Inch" in a pull down list. In cell A2, the value is entered. In cell A3 i want to show the value in the other units. So if A1 is Metric, then take A2 and divide by 25.4. And if A1 is Inch, then take A2 and multiply by 25.4. Also, if A1 is Inch, then display 2 decimal places in A3, and if A1 is Metric, then display 3 decimal places in A3. Is this possible?
View Replies!
View Related
Converting Feet/inches To Decimal Equivalent
I am new to Excel but not programming and I am looking for a recommendation for the following. I have a spreadsheet that simply takes the length and width of an area and computes the square feet and yardage and other sundry items. I am entering the feet/inches as follows: Example: 11.3 (equals 11/ft 3/inches) The correct decimal conversion should be 11.25 but, obviously, it does not know that the number to the right of the decimal point is an indicator of inches. (ex.: .5=.42, .7=.58, .9=.75, .11=.92) I have approached this from the stand point of an IF condition, finding the position of the "." and grabbing everything to the right (+1) but I understand that the limitation is 7 nested IFs. Can someone get me kick-started on what the best approach would be to get my entry to convert to the true decimal equivalent? Currently, I am simply doing the conversion from memory but I would rather automate this sometimes errant approach.
View Replies!
View Related
Converting Decimal Numbers To Minutes And Seconds
I have an excel sheet with a row of numbers example below. These numbers represent the length of time that a telephone call was. The problem is they are decimal numbers (units of 100) but they represent seconds and minutes of a phone call which change up one number when the seconds hit 60 ...
View Replies!
View Related
Converting Decimal Numbers To Text With Dot Numbers
we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus. With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers: Sub ForceToNumber() Dim wSheet As Worksheet For Each wSheet In Worksheets With wSheet . Range("IV65536") = vbNullString .Range("IV65536").Copy .UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd End With Next wSheet End Sub Source : http://www.ozgrid.com/forum/showthre...087#post184087. The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.
View Replies!
View Related
Automatically Change The Point Name To Match The Point Name In Column
I have a pivot table as shown below which is pretty straight forward however I am trying to create a macro that will automatically change the point name to match the point name in column E then copy that resulting dispaly to another sheet then pick the next point name in line and do the same thing and repeat for 50 rows, so my end result will be 50 pivot tables ready for printing. I can do this manually but I am trying to make it automatic...BTW the point names in column E change everyday but the pivot table supports the name changes. Option Explicit Sub PointName() Dim Ws As Worksheet Dim Rng As Range, Cel As Range Set Ws = ActiveSheet Set Rng = Range(Cells(2, 7), Cells(Rows.Count, 7).End(xlUp)) For Each Cel In Rng Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " " Ws.Columns("A:B").Copy Sheets.Add With ActiveSheet .Paste .Name = Trim(Cel) .Range("A1").Select End With Next Ws.Activate End Sub I turned it off for a while and when I turned it back on I am getting an error Unable to set the _Default property of the PivotItem class Debugger is highlighting Ws.PivotTables("PivotTable1").PivotFields("Point Name").CurrentPage = Cel & " "
View Replies!
View Related
Paste Two Decimal Number In Excel Without Extra Decimal Places Appearing
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 Replies!
View Related
Inserting A Comma
I am working with large sequences of letters and I'm trying to put them into their own columns. I know about the 'text to columns' tool but the problem is my sequences are not delimited by anything. I would have to think the easiest way to accomplish what I want to do is to insert a comma, space or other delimiter between every letter, then use the text to columns feture. EX. I'm looking for something that does this... AAAAAA --turns into--> A,A,A,A,A,A or A A A A A A
View Replies!
View Related
Comma Separated Data
I have four columns of data. Column A is for the Model, B is for options, C is for color, and D is for Trim. What I need to do is for each model I need to have the options in column B to be listed down the page without commas. Each 3 code option needs to have it's own cell. In the below example there are 8 models with their options, color, and trim. I cannot have the options mixed up with the other models. Hope this makes more sense. Here is the raw data ...
View Replies!
View Related
Remove The Last Comma In The String
The below function concats a range of cells by csv. How can I get it to remove the last comma in the string when it's finished? Function SpecialConcatenate(rnge As Range) As String Dim r As Long, col As Integer For c = 1 To rnge.Columns.Count For r = 1 To rnge.Rows.Count If rnge.Cells(r, c) "" Then SpecialConcatenate = SpecialConcatenate & _ rnge.Cells(r, c).Value & "," End If Next r Next c End Function
View Replies!
View Related
Puts A Comma Before The Last Two Digits
I did post a problem where I have a number like 123456 and I need to have Excel change it so that it puts a comma before the last two digits .. like so: 1234,56 I got a reply where I got the solution to use 0","00 and this works in Excel (using the custom format) The only problem is that although the number changes in the cell to 1234,56 it doesn´t do so in the FX window and thus when I use the number to multiply it is actually 123456 instead of 1234,56 like it want it to be.
View Replies!
View Related
Separating Items With Comma
i have many many cells that have text like the following: A,B,C,D,E,etc... what i would like is to have it show like the following instead (within the same cell): A B C D etc... i know i can do this using ALT+Enter to make the separation manually....but there are many many cells....can this be done automatically? also, it is possible that some cells would have only 1 item so in that case, it should be left alone....
View Replies!
View Related
Space After Comma In Names
I have a list that contains names in the format Lastname, first name But some of the names have no space after the comma - which is what I am trying to achieve here For example, Citizen, Joe Citizen,Joe I would like to insert a space after every comma in the name, when there is no space. I am looking at a formula based approach to solve this.
View Replies!
View Related
Replace Blank Space With Comma
I have the following code which is save Sheet as a text file. I know there are some options in Excel for saving, but it is not saving as ".txt" file. I would like to modify this code to replace blank space with comma between Cells. PHP Private Sub CommandButton1_Click() Application.DisplayAlerts = False ' Save file name and path into a variable template_file = ActiveWorkbook.FullName ' Default directory would be c:temp. Users however will have the ability to change where to save the file if need be. ' Notice that i'm only allowing the save as option to be of .txt format. ' I'm also attaching the current date to the file name............
View Replies!
View Related
Match Function To Find Name With Comma
I have a spreadsheet in which I use a match function to find the row a name is on. The name may be listed several times and the name has a comma in it, for example, "Tom, George". I got it to work once, but it doesn't always work. Is it because of the comma or because it is in the lookup several times? It's my understanding the Excel will return the row number for the first time it sees the name in the list, which is what I want.
View Replies!
View Related
Text File But Comma Delimited
In my workbook I have 10 columns with data, starting at A12 and down. This could be several hundred rows. I would like to export the first three columns only (Column A, B and C). The TXT file should have the following: First line: "This file was exported from Excel" Second line: The value of cell B4 of the worksheet Third line: The value of cell B5 of the worksheet Fourth line: Today's date ( as 27/Sep/2007) Fifth line: No entries (Empty row) Sixth line will be the first numeric transfer. This is cell A12 value, cell B12 value and cell C12 value. Seventh line: Cell A13 value, cell B13 value and cell c13 value. etc, until the end or selected amount of rows. The values of the different columns should be separated by commas Column A values could be 9 characters (5 numerics with 4 decimals - 12345.6789) Column B values could be 10 characters (6 numerics with 4 decimals - 123456.7890) Column C values could be 10 characters (6 numerics with 4 decimals - 123456.7890) The text file therefor would look as follows: This file was exported from Excel. Company ABC On this continent 27/Sep/2007 123.4567, 23.7654, 123.4567 1234.5678, 123.4567, 987.6543 12345.6789, 6.0000, 2.9876 I tried to adapt jindon's code but no luck. I am currently doing it the long way. ='[Workbook1.xls]MySheet'!$A12&", "&'[Workbook1.xls]MySheet'!$B12&", "&'[Workbook1.xls]MySheet'!$C12 This works but I really liked what jindon did and wondered if it could be adapted to fit my needs.
View Replies!
View Related
Search Comma Delimited String
I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found. For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years. I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?
View Replies!
View Related
Replace Comma With Dot In TextBox
I want to set at my textbox,so that if user enter the number with comma instead of dot,the comma will automatically be changed into my default format(dot).for example,if user type in (86,5),the value will be converted to (86.5). I have read through the relevant previous thread regarding this matter and I wrote the function as below.It happens to be that (86,5) will change to (865.00),not as what it suppose to be.So can anyone correct my function below? Private Sub txtdiameter_Change() If txtdiameter = vbNullString Then Exit Sub If Not IsNumeric(txtdiameter) Then MsgBox "Numbers Only" txtdiameter = "86" 'default value' End If End Sub
View Replies!
View Related
Have 300 Cells, Want Them In One, Seperated By Comma
I think this is a really easy question but I can't seem to think of an easy answer. I have 300 cells in one column I want to have combined into one cell, seperated by commas. I know I could just =A1&","&A2&","&... but this is not what I want to spend my afternoon doing. Is there a better way of doing this?
View Replies!
View Related
Find , (comma) And Return All Characters After It
Excel 2007 - Windows XP I have the below in cells A1 and A2. I need to find the last comma then return all characters after it. I need the same formula for both cells because the value is always changing. HUG,PU-TUB,BW,-,-,64 Need to Return 64 in B1 HUG,PU-RFL,BW,-,-,176 Need to Return 176 in B2
View Replies!
View Related
Comma Separated Values In A Cell..
I have a cell in a table containing user entered Comma separated values. How can I process it so that user entered values can be referred to just like in an array? Is it possible to do something like cell.value[0]? For example -- cell A1 has values red, blue, green. B1 has a formula that can refer to "red" in A1 by writing something like A1.value[0] Is it possible to have a function return values to another cell reference than from the cell where it is called? Like a UDF called functionIndividualValues(InputcellReference, OutputCellReference) functionIndividualValues(A1, A2) meaning that the input string for the function is in A1 and the output should go to the cell A2.
View Replies!
View Related
Replace Comma With Hard Return
I have a string in a cell which is essentially comma separated and want it in the same cell but with enters instead of commas. Cell info to start with would look like this Bob, The, Builder And the result I am looking for is Bob The Builder All within the same cell so I can create a label which uses this information on a user form. I have attached a small example.
View Replies!
View Related
Add Comma To Each Cell In Column
I am an Excel Novice. I don't know very much at all about how it works, what formulas are or any of its intracacies. My job requires me to take information from an Excel file that is emailed to me. I need to know in the simplest terms how to take the first column (which is last names) and add a comma to the end of each name (one in each cell) all the way down that column, before I copy and paste them, so they will read "last name comma space first name".
View Replies!
View Related
Price Point Rounding
the rate and inventory management system my hotel uses rounds up to the next .95 currently. Most rates are based upon a 'Rack Rate' in the form of a discount percentage. So, if the Rack Rate is $129.95 and the AAA discount is 20%, the resulting $103.96 rate is rounded up to $104.95. I'm trying to fix my spreadsheet to account for the various rates and discount percentages of several different types of discounts. I've tried a couple rounding methods: =ROUNDUP(B$30*(1-B32),0)+0.95 which returns the correct value only when the value to the right of the decimal point is .96-.99 =ROUNDDOWN(B$30*(1-B32),0)+0.95 which returns the correct value only when the value to the right of the decimal point is .00-.95 =CEILING((B$30*(1-B32)),0.95) which returns some odd bits, sich as .85 and .50 So, is there a way to get Excel to shift the rounding base point from zero to .95? Or some other way to accomplish this?
View Replies!
View Related
|
TRACKER
