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.
View 6 RepliesI'm looking for the VBA command for this function. I tried just recording a macro in where I perform the task but it didn't record anything. Tried it several times even.
View 7 Replies View Relatedwe 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.
I have a HTML table with a column with numbers like
3.6
2.5
1.8
....
and so on...
When I'm trying to open this table in Excel (.csv or if I cut n paste from HTML) Excel keeps converting it to date format.
03.jun
02.may
01.aug
This wouldn't be a problem if I could only go to Format -> Cell and choose Standard or something.
The problem is it ends up like this then:
39236
39204
39295
WTF? :D
I want this!
3.6
2.5
1.8
....
I'm having issues with my code for converting numbers to words I've tried a couple and I keep getting a Syntax error on
Cents = GetTens(Left(Mid(MyNumber, DecimalPlace + 1) & _
"00", 2))
I've tried a couple of different codes and I keep getting errors
yes i have enabled macros
I am receiving a monthly breakdown of postage costs by department but the values are received in a text format of 1 234,56.
I need to convert these to numbers.
I have replaced the comma with a full and tried to use =substitute(cell ref," ","") to remove the spaces in those numbers exceeding £1000 but this does not seem to work.
Postage.xlsx‎
How can i convert text columns to numbers in VBA.
View 8 Replies View RelatedI have 0,1,2,3,4,5...etc in Column A. How I could convert this into time format? Preferably with vba code?
Sheet1 Â AB
1001/01/2012 00:00:002101/01/2012 00:00:013201/01/2012 00:00:02
4301/01/2012 00:00:035401/01/2012 00:00:046501/01/2012 00:00:05
I have this value in a cell 38761.8296804781 which is displaying as 38,762. I want to display it as $38,761,829. Is this possible without going through each cell and moving the decimal point?
View 3 Replies View RelatedWe 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.
View 7 Replies View RelatedI 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).
Is there any easy way to convert these?
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?
View 1 Replies View RelatedI have numbers from -50 to 50 going down column C. In column D, I want to enter a formula which will convert each number to a TRUE or FALSE. How do I do that?
I believe 1 = TRUE and everything else = FALSE, but I would like to know how to get that with a formula.
I'm opening a file in Excel, and it automaticaally changes a lot of the numbers to dates. How can I turn this off in the settings panel?
View 10 Replies View RelatedHow do I convert hours into numbers and/or decimals?
Example:
Column A Column B
---------- -----------
30:05:00 to 30.05
26:10:00 to 26.10
262:47:00 to 262.47
Figures under Column B refer to info that I would like to get.
how we could convert numbers to 24 hour time format as we key in the numbers?
Example:
As we key in 0815, it would automatically convert to 08:00.
As we key in 1345, it would automatically convert to 13:45.
What is the code we could use to put in the code of the sheet?
Has anyone created a marcro that will convert all the worksheets to Hard number, changing the formula results, (by Copying and paste, special, value). all at once?
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.
Any easy function to convert numbers into words?
I a looking for an easy function to transform numbers into words as shown :
Value------Output :
12624-----Rs Twelve thousand six hundred and twenty four only
12624.24--Rs Twelve thousand six hundred and twenty four and twenty four paise.
Hopefully the currency should be changeable ( dynamic) i.e Rupees to Dollars..
In India , we also use lacs and crores , so something in those lines if possible...
I am having trouble converting time cells into numbers.
The cell I'm looking at visually shows "135:42:00". If you look at the formula bar, it reads "1/5/1900 3:42:00PM"
How do I start a series of conversions to get an adjacent new cell that would read 135.7?
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....
View 7 Replies View Relatedhow to create some code that can convert any column number to it's respective letter, from 1 = "A" to 16384 = "XFD". The code is posted below. Enjoy, or let me know how you would improve it.
Code:
Function colNumToChar(colNum) As String
Dim Num1, Num2, Num3 As Integer
Dim Let1, Let2, Let3 As String
[Code]....
I want to convert numbers that were entered into a spreadsheet with commas in them. Since they were entered with commas of course excel reads them as text.
How can I convert them to numbers so I can use formulas.
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'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?
View 1 Replies View RelatedWe 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?
View 2 Replies View RelatedI 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 9 Replies View Related