Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting numbers stored as text into number-format
I haven't seen your file but.........
Select an empty cell, press <Ctrl<C Select the range of text you wish to convert. Right click, select Paste Special, tick Add and click on OK. Your text should now be numerals "Eirik Sævareid" wrote: When issuing a report, I have a need for extracting data from a production control system. The data are being saved in HTML-format, and then saved into Microsoft Excel. The numbers then occur stored as text-format in Excel. I have not succeded when trying to convert them into number-format (the numbers are going to be used in further calculations). Please see the attached Microsoft Excel-file. Have anybody the solution for my problem ? The report is bigger than the sample in the attached spreadsheet. All help will be greatly appreciated. Best regards, Eirik Sævareid |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting numbers stored as text into number-format
Enter and run this small macro:
Sub numerify() Dim r As Range Count = 0 For Each r In ActiveSheet.UsedRange If Application.IsText(r.Value) Then If IsNumeric(r.Value) Then r.Value = 1# * r.Value r.NumberFormat = "General" Count = Count + 1 End If End If Next MsgBox (Count & " cells changed") End Sub -- Gary's Student "Eirik Sævareid" wrote: I am sorry, but nothing happens when I try that...(nothing is copied). Maybe I misunderstood or I do somehting wrong... Eirik "kassie" skrev i melding ... I haven't seen your file but......... Select an empty cell, press <Ctrl<C Select the range of text you wish to convert. Right click, select Paste Special, tick Add and click on OK. Your text should now be numerals "Eirik Sævareid" wrote: When issuing a report, I have a need for extracting data from a production control system. The data are being saved in HTML-format, and then saved into Microsoft Excel. The numbers then occur stored as text-format in Excel. I have not succeded when trying to convert them into number-format (the numbers are going to be used in further calculations). Please see the attached Microsoft Excel-file. Have anybody the solution for my problem ? The report is bigger than the sample in the attached spreadsheet. All help will be greatly appreciated. Best regards, Eirik Sævareid |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting numbers stored as text into number-format
Hi Erik
Numerals entered as text are left aligned. When you copy an empty cell, then select a cell with left aligned numerals, right click and selct Paste Special, tick the Add box and click on OK, Excell adds the value 0 to the cell you are pasting to. The contents of the cell should then right align. You have to highlight the range you want to change. Say for example you have an empty cell in range K1. Go to K1, press <Ctrl<C, and without doing anything else in between, select your data range. Let's say this range is A2:A50. You block range A2:A50, move your cursor anywhere in this range, right click your mouse, select Paste Special. In the window that pops up, make sure you tick Add, and click on OK. If your cell contents do not right align, there may be another problem. You may have spaces between digits, such as 123 456, iso 123456. If you post examples of your data, maybe someone can help you. Uploading the file does not always work, though. If you still cannot resolve the issue, send me your file to jDOTkasselmanATlanticDOTnet. Just change the address to the correct signs "Eirik Sævareid" wrote: I am sorry, but nothing happens when I try that...(nothing is copied). Maybe I misunderstood or I do somehting wrong... Eirik "kassie" skrev i melding ... I haven't seen your file but......... Select an empty cell, press <Ctrl<C Select the range of text you wish to convert. Right click, select Paste Special, tick Add and click on OK. Your text should now be numerals "Eirik Sævareid" wrote: When issuing a report, I have a need for extracting data from a production control system. The data are being saved in HTML-format, and then saved into Microsoft Excel. The numbers then occur stored as text-format in Excel. I have not succeded when trying to convert them into number-format (the numbers are going to be used in further calculations). Please see the attached Microsoft Excel-file. Have anybody the solution for my problem ? The report is bigger than the sample in the attached spreadsheet. All help will be greatly appreciated. Best regards, Eirik Sævareid |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need help converting Number stored as Text to Date | Excel Discussion (Misc queries) | |||
Converting numbers stored as dates to text in Excel | Excel Discussion (Misc queries) | |||
macro for converting number stored as "text" (or preceeded with ') to "number" formatting | Excel Programming | |||
Number stored as text. How do I keep the number format? | Excel Programming | |||
how to format numbers stored as text or vice versa to use vlookup | Excel Worksheet Functions |