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.
im working on making a price list from a pdf file. however, the pdf file has the prices in euros listed as 1.300,00 and 765,00 and 45,00 and 1.565,00 . i was wondering if there was a tool or so in excel that can change the comma and periods in the number so they read as american format like 1,300.00 and 765.00 and 45.00 .and also if there is a formula or so to convert these numbers to Dollars in another column.
We are doing a database conversion and I have been tasked with converting all our old promotion codes to a new system. The long and the short of it is this:
I am trying to write an equation that will look at two cells (say A1 & B1) which when put together could be one of the following numbers (01,02,03,04,05,06,07,08,09,10,11,12) and translate them to a letter (A,B,C,D,E,F,G,H,J,K,M,N)
So 01=A, 02=B and so on...
I was trying to use if statements, but there are too many.
Do you know if you can use the cell formatting to change the sign of a number? For example, the cell contains 123.45, but I want it to show as -123.45. I don’t want to use a formula to multiply by -1, because the data is being extracted from a database and I have to do a fair amount of work on the report with the formulas to do that.
I can format the number and it shows a minus but will not use the minus if I then use it in a calcualtion. I could use a calcualtion with a *-1 in it but for ease of use by other users who may take part of the Excel report it would be better if I could format the number to negative.
I am using decimal and hexadecimal numbers. On a worksheet I can use all of the normal functions for this problem (Hex2Dec etc). I have loaded the Analysis toolpack, and also have an add-in loaded (but do not know how or when) that is called Analysis ToolPack VBA. In VBA I can use the fnction Hex to convert from decimal to hex, but there does not seem to be a reverseof this function. Am I missing something obvious or is this a 'hole' in the available functions? Is there a reference I ned to add in the VBE? if I try to use the worksheet function in VBA then I get a Value# error.
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).
I received a complaint from one of my colleague that he is facing sudden problem with Ms-Excel(2002) The problem is When he feeds number 2 in to a cell it automatically converts in to 0.02. I tried using "Decrease decimal" and "format cells" option and even through uninstalling office and reinstalling a different version but the problem still continues.
I have exported a dataset with references numbers and I want to convert those reference numbers into the titles they refer to. I have a separate sheet that matches the reference number to the title and would normally use a LOOKUP function [=LOOKUP(A2,Key::Table 1::A,Title)] to do this but, in this instance, I have multiple reference numbers in a cell separated by commas. How can I get the function to lookup multiple numbers from the same cell?
I have a spreadsheet which has the date set out like this: 20070101
If there any way i can change this to either 2007/01/01 or 01/01/2007? I have tried using the date format (in the cells) but it just comes up with ######## i tried expanding the cell but no luck, this is very importand that i do this as its for a customer.
I need a way to randomly generate a number( up to hundred trillion) in cell A1 and in cell B1 have the number appear in word form. Example: 129,114,023,131,453 will appear in A1 and in B1: one hundred twenty-nine trillion one hundred four-teen billion twenty-three million one hundred thirty-one thousands four hundred fifty-three The hyphens are important, commas between the numbers are not necessary. I know that for A1 I can use Randbetween() function to generate the numbers. The problem is generating the word form with the hyphens....
I have a file with a lot of numbers, but one column with single digits. These digits have a cooresponding model name. I want to convert them all to their model name. There are only 4 model names. I tried a nested IF statement, but felt this wasn't working properly or was not the proper formula choice.
A Find/Replace would work, but it picks up the single digits elsewhere in the sheet. I realize i could copy/paste my column to a new sheet and from there convert/paste, but I am trying to get better at Excel.
I have hundreds of .dbf files that I am manually converting to .csv using Excel. Unfortunately, my .dbf collection is growing faster than I can convert them. Is there a way to convert all the .dbf files in the current folder to .csv? All the .dbf files are in the same folder.
The path to them would be the same as the macro file.
I've been having a problem lately with Excel automatically converting all of the cells on my spreadsheet from standard number format to dates. I've created several spreadsheets for calculations and all the cells in the number formats that I wanted. It's been saved and actually reopened and used several times. I opened it this morning and now half of numbers are now converted to dates. I've had this happen several times on several different spreadsheets. Why is this happening? Is there a setting somewhere that I need to change?