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
ADVERTISEMENT
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
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
Aug 15, 2008
I am using the code below which reads and exports the data to a text file.
1) the result is written to the text file with each line in quotation marks. As I want to subsequently change this text file to a batch file is there a way of the not having the quoation marks
2) As you will see the code looks for the text file in a specific folder on my PC, is there a way of it creating it's new txt file in the same folder that the spreadsheet is located.
3) is there a way of excel changing the file attribute from .txt to .bat
View 11 Replies
View Related
Aug 15, 2008
I've created a spreadsheet to create a text file which I can use to inport values into another device. Using CONCATENATE etc I end up with a range of cells (1 column / Many rows) that I can then MANUALLY highlight, choose copy, open the text file, paste in the contents and then save it. This works fine but as you can imagine I'd like to automate the proceedure by having excel create and save the text file for me.
From what I've seen on these forums most people are wanting CSV type files but not me. I've already got the cell contents pieced together it just needs writing to a text file, no delimiters, no quote marks, just exactly what's in the one cell on one line, it's just one column remember (H2:H1456).
I'm not bothered about appending data either, a hard coded overwrite of the text file is fine by me.
The actual file type I ultimately need is an .abk file, but like I say this is just a basic text file that's been renamed. Could Excel create this or must it have the .txt file extension?
View 11 Replies
View Related
Jan 13, 2009
I'm trying to to take a picture from a Worksheet and save it back to file in a Windows folder via macro.
I've been searching thru Excel help and internet and I didn't see any solution.
View 2 Replies
View Related
Feb 14, 2012
I have about 180 Excel files (each one with 51 columns and around 30,000 rows) that need to be exported to an Access table.I'm using the routine below which is extremely time-consuming. I'm sure there is a better way to export an excel file to an Access table.
Sub ExportHistData()
Dim rst As Object
Dim cn As Object
Dim i As Long
Dim lstCell As Long
Application.ScreenUpdating = False
lstCell = [a65536].End(xlUp).Row
If lstCell = 1 Then Exit Sub
[code].....
View 4 Replies
View Related
Apr 6, 2009
The sheet to be exported is titled TI
I need the name of the name of the txt file to use the values in cell AE1 & Q1 from a sheet titled Project Report. eg cell AE1 contains 1783 and Q1 contains Ore Vally so the file name needs to be 1783 Ore Valley.txt
In the sheet TI, under column A, there are cells that contain the number 0. I want the export to ignore any rows which have a 0 under column A
The data from each colum in the sheet TI needs to be separated by TABs and not commas, ful stops, semi colons, colons etc etc.
Once all of this works, i need to be able to just click on cell J1 in sheet Project Report so that with this single click the txt file is produced.
View 9 Replies
View Related
May 6, 2014
I've attached 2 test files, one is the database master file containing the projects (each row represents a project, unique reference number in column A) and the other is the blank template file i'm hoping to export data into and then save down with the naming convention "column A_column B.xlsx"
Kept the test files simple but would need to modify any code to apply to much larger database consisting of many more fields etc.
Master.xlsx‎
Template.xlsx‎
View 9 Replies
View Related
Jun 28, 2007
I've been doing this excel worksheet for work; even though I've never used excel macros or VBA before.(So if this is an easy question to answer don't laugh at me. :P) (I have worked with other programming languages though.) Anyway, using a VBA manual, I've done pretty well, until now. I'm trying to make a macro that when one clicks on the button it saves the file as a text tab delimited file, but giving it the name of whatever the user put into a specific cell. (And also without closing the excel file too, so they can add more if they have to) So I've gotten it now so that it saves with the correct name, but I am unsure about how to make it save as the text file I want. (At the moment it saves as an unreadable file) Any help would be great thanks.
Here is the ...
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
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
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
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
Oct 17, 2008
I have a column with a number of strings looking like below examples
View 3 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
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
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
Jun 10, 2009
I need to convert a column of numbers currently formatted with 2 decimal places e.g. 112.12 to 4 decimal places (without the decimal point). I need the end result to be 1121200. I've tried a few different suggestions given on the forum previously but can't seem to retain the 4 decimal places that I require.
View 4 Replies
View Related
Apr 15, 2012
I have to make a excel document in which I have length and width in feet and inch format.
E.g. 10.01 in which 10 is feet and .01 is inch
I have all the length and width values in the above format. And what I want to do is convert the inch value (10.01) to feet value (.01=.08 feet) .
Just like the calculator here does.. [URL] ........
Like
10.01=10.08
10.02=10.16
10..03=10.25
...and so on...
Here is table of conversions from inches to decimal feet. But I don't know how to get a formula for this in excel...????
Inch Decimal of a Foot
1 inch 0.0833
2 inches 0.167
3 inches 0.250
4 inches 0.333
5 inches 0.417
6 inches 0.500
7 inches 0.583
8 inches 0.667
9 inches 0.750
10 inches 0.833
11 inches 0.917
12 inches 1.000
View 7 Replies
View Related
Aug 8, 2008
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 2 Replies
View Related
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
Jun 10, 2007
I need a formula to multiply only the decimal number in a cell and not the integer. For example: the number in the cell is 57.3615. I want to multiply .3615 only.
View 2 Replies
View Related
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
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
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
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
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
Feb 27, 2014
I need a formula to take a number with several decimal places and round it up to two decimal places to either .33, .66 or, .00 if its above .66.
For example, 4.23423423423423 will be 4.33
4.43453453533434 will be 4.66
4.8353453453 will be 5.00
Lets say the number is in cell A1. What formula would do this?
View 3 Replies
View Related
Dec 26, 2006
I used Excel to take inventory of the items in my store.
I have two columns that I would like to export into my point of sale program.
They are, "product code" and "inventory count". When I try to copy them into my pos system it won't work because I would have needed to hit the enter or tab key on the keyboard inbetween the two fields. So if I were going to enter it all by hand I would just type the product code, hit enter or tab and then type the quantity.
Is there anyway to make Excel export an Enter key press or tab key press inbetween the two columns? If I can't figure it out I will have to do it all by hand. (over 2000 items)
View 9 Replies
View Related
