I am using an ODBC database connection to input data into an excel file. When I input the data excel stores the numeric data as text and puts a ' in front of the number. There is an exclamation mark beside the cell which allows me to convert the data back to numeric format. I need excel to do this conversion for me automatically when new data is added to the file.
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.
When I use the SUMIF(S) function and a criteria range that has numbers stored as text (eg: '394) the unequal operator (<>) doesn't work.
=SUMIFS(B:B,C:C,"<>493") doesn't work
I'm able to get the salary of employee 493 by using "493" instead of "<>493", but it seems that with the unequal operator Excel starts treating this ID as number. The only work-around I found so far is to use a wildcard e.g. "<>*493" or "<>493*". Excel then treats it as text again, but this is dirty workaround. What happens if an employee with an ID e.g. 1493 shows up!?
"<>'493" or something doesn't work unfortuantely..
There is probably a really easy answer to this question but I cannot seem to figure it out. I need to calculate the sum of pages faxed on a fax report at my office on an excel spreadsheet which is downloaded from our server. It appears the file is not meant to be used in excel but it downloads as an "*.xls" file anyway. When I open the file I receive this message:
"The file you are trying to open, 'FaxCetailReport.xls', is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Do you want to open the file now?"
The file is comnig from a trusted source so I select "Yes" and it opens just fine. There are just over 50 rows of info, one of them being the number of pages. In a blank cell I enter "=SUM(select rows)" and the result is a "0". I have converted the cells to the "number" format, no change. When I right click on the cells and select format, the protection tab on the right has the check box "Locked" checked and unchecking it does nothing, it is checked as soon as you go back in to the protection tab.
The only way around this is to manually retype the numbers in a cell next to the pages column, which is ok for 50 numbers, but I have some sheets where there is over 1,000 entries.
I have the following macro that I use to convert numbers stored as text to text. For some reason, if I try to use it twice in a row, it won't work, I get an error that says "application-defined or object-defined error" and highlights this part of the Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 1).Copy
I have five columns on an Excel Spreadsheet with questions in each of the columns.
Below each question is a number value. In the rows below these number values the users will be answering the questions with Y or N. When the spreadsheet is complete I would like to replace the Y's with the number value shown under the question and replace the N's with a number value of zero (0).
We are compiling annual receipts - using Excel spreadsheet numbers to merge into a Word document, but we are losing the cents in the merged document. When we tried converting to text, the cents were lost as well. Example 450.00 becomes 450 - how do we keep the cents - 450.00?
I have an Excel workbook with around 100 worksheets. All of them have similar columns, but the values are stored as text. The length of columns and missingness is different in different worksheets. I will be grateful if any of you could suggest a VBA that could convert the numbers stored as text back to numbers. I have been looking for an answer for a while, but cant seem to find one that automatically looks at every worksheet and converts text into number.
I attached partial file so you can see what i mean. I pasted only a few combinations cause the file was to big and i wasnt able to attach it...there are 142506 combinations . But you can see what i mean. A VBA or a macro on the worksheet will do the job?
I have a text file with rows and columns of numbers ranging from 1-4 digits that I'd like to import/copy into Excel with each number being in its own cell. But whenever I copy/try to import, Excel splits all of the 3-4 digit numbers up into single digit numbers. The text file has 10,000+ columns (each number occupies two columns so I have half of that amount in numbers) and 300+ lines.
Is the file simply too large for Excel to handle or is there a way I can do this?
A B C D E F G 23445566894 Brwn pdc aft Cat1 45687930596 $5 45687930596 Gld wdget adi Cat2 23445566894 $6
In the example above, I would like to do a vlookup in column C that matches the digits in column A to those in column F, with the output being the corresponding value in column G. I believe it involves the left function but not sure how to really use it here.
I'm working on a spreadsheet with about 400 rows and 10 columns. All the numbers in the columns are formatted as text, which doesn't allow me to use AutoSum. I have selected the cells and formatted them as number with no decimals, but AutoSum still doesn't work because the formatting hasn't changed and they're still formatted as text. I know if I double click each cell, the cell changes the format from text to number, but that will take a lot of time to change 400 cells in each of the 10 columns. What can I do to quickly reformat the cells from text to number?
I am looking for a VBA to sort rows which include actual numbers and text representing decomposed CTQs (or procedures in IT development)
Col A Col B 1Billing Accuracy 2Billing Time 3Credit Check Accuracy 4Credit Check Time 2.1Bill preparation
This is the order in which the data is copied and saved from worksheets in which they are developed. Note that 3 rows (22.214.171.124 through 126.96.36.199) are below 8.1.3 (because the three come from Worksheet 8.1.2 which came after worksheet 8.1). The first four rows came from a Top Level Worksheet. I would like to see them intermixed but in proper order.
I need to compare two colums by number decription for example m344 in one column and fsh344-1 in another. All I want to match is 344. In column a I want to indcate the match by placing an X by each match. View my attachment for reference. I don't know if it makes a difference but the columns are centered in my original spreadsheet.
See attached file. A colleague is downloading rows of data from a website which contains a number field Excel is currently treating as Text after being pasted in. My spreadsheet includes just a sample of the many rows of data however as you can see the VALUE function refuses to convert these text values to numbers. How these might be converted and why the VALUE function refuses to work in this case?
I am looking for a formula to grab only the numbers withing a cell that has text in it i.e (John Peters142 lp) or (Sally Jones54) or (Terry Cram310 jkb) sometimes there will be text after the number and sometimes not, the format is as shown with no space between name and number but a space after the number.