Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
I have a column of numbers but some is stored as text and some as numbers. I
would like to convert all of them to numbers in a macro but don't know how. I've tried recoreding a macro when selecting the list and clicking on the convert to number but the macro doesn't seam to record that part. Help please. Donna |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
Try this:
Copy a blank cell Then select the range of "numbers" From the Excel main menu: <edit<paste special Check: Add Click the [OK] button Does that convert all of them to numbers for you? *********** Regards, Ron XL2002, WinXP "Donna S" wrote: I have a column of numbers but some is stored as text and some as numbers. I would like to convert all of them to numbers in a macro but don't know how. I've tried recoreding a macro when selecting the list and clicking on the convert to number but the macro doesn't seam to record that part. Help please. Donna |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
Ron's solution is best, but if you really need a macro then:
Sub numerify() Dim r As Range Count = 0 For Each r In Selection 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 Converts both text cells and apostrophe'd values. -- Gary's Student "Donna S" wrote: I have a column of numbers but some is stored as text and some as numbers. I would like to convert all of them to numbers in a macro but don't know how. I've tried recoreding a macro when selecting the list and clicking on the convert to number but the macro doesn't seam to record that part. Help please. Donna |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
Ron This works. Thanks.
Donna "Ron Coderre" wrote: Try this: Copy a blank cell Then select the range of "numbers" From the Excel main menu: <edit<paste special Check: Add Click the [OK] button Does that convert all of them to numbers for you? *********** Regards, Ron XL2002, WinXP "Donna S" wrote: I have a column of numbers but some is stored as text and some as numbers. I would like to convert all of them to numbers in a macro but don't know how. I've tried recoreding a macro when selecting the list and clicking on the convert to number but the macro doesn't seam to record that part. Help please. Donna |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
Anyone have an answer about the other way.
We run a report from a Cognos report writing and it dumps to excel. We recently had the Office SP2 installed and now some of our test data fields are showing a numbers only. We've tried everything to get them to change to text. Paste special, formatting, format paintbrush, adding apostrophe, removing the date - all kinds of different things. Does anyone have an idea of a solution or a cause? "Gary''s Student" wrote: Ron's solution is best, but if you really need a macro then: Sub numerify() Dim r As Range Count = 0 For Each r In Selection 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 Converts both text cells and apostrophe'd values. -- Gary's Student "Donna S" wrote: I have a column of numbers but some is stored as text and some as numbers. I would like to convert all of them to numbers in a macro but don't know how. I've tried recoreding a macro when selecting the list and clicking on the convert to number but the macro doesn't seam to record that part. Help please. Donna |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
Are you saying that the same Cognos-to-Excel report displayed the field as
text pre-SP2, but displays numbers now? Example: Was: 0032 Now: 32 OR...are you importing a Cognos gererated text file and that's the new behavior? *********** Regards, Ron XL2002, WinXP "Erinayn" wrote: Anyone have an answer about the other way. We run a report from a Cognos report writing and it dumps to excel. We recently had the Office SP2 installed and now some of our test data fields are showing a numbers only. We've tried everything to get them to change to text. Paste special, formatting, format paintbrush, adding apostrophe, removing the date - all kinds of different things. Does anyone have an idea of a solution or a cause? "Gary''s Student" wrote: Ron's solution is best, but if you really need a macro then: Sub numerify() Dim r As Range Count = 0 For Each r In Selection 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 Converts both text cells and apostrophe'd values. -- Gary's Student "Donna S" wrote: I have a column of numbers but some is stored as text and some as numbers. I would like to convert all of them to numbers in a macro but don't know how. I've tried recoreding a macro when selecting the list and clicking on the convert to number but the macro doesn't seam to record that part. Help please. Donna |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
It displays the words but it is formated as number type fields not text type
fields. If you go into FormatCell all of the text type formatting choices show ################### but the numeric type choices are fine. We then use the date from the Excel spreadsheet and mail merge it into a word document - which is not picking up those fields now. Also, an interesting twist when you do a filter on the column - it doesn't recognize it as anything - when you choose "Blanks" from the filter dropdown it brings up the true blanks plus those fields. "Ron Coderre" wrote: Are you saying that the same Cognos-to-Excel report displayed the field as text pre-SP2, but displays numbers now? Example: Was: 0032 Now: 32 OR...are you importing a Cognos gererated text file and that's the new behavior? *********** Regards, Ron XL2002, WinXP "Erinayn" wrote: Anyone have an answer about the other way. We run a report from a Cognos report writing and it dumps to excel. We recently had the Office SP2 installed and now some of our test data fields are showing a numbers only. We've tried everything to get them to change to text. Paste special, formatting, format paintbrush, adding apostrophe, removing the date - all kinds of different things. Does anyone have an idea of a solution or a cause? "Gary''s Student" wrote: Ron's solution is best, but if you really need a macro then: Sub numerify() Dim r As Range Count = 0 For Each r In Selection 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 Converts both text cells and apostrophe'd values. -- Gary's Student "Donna S" wrote: I have a column of numbers but some is stored as text and some as numbers. I would like to convert all of them to numbers in a macro but don't know how. I've tried recoreding a macro when selecting the list and clicking on the convert to number but the macro doesn't seam to record that part. Help please. Donna |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
To convert sheet to text try copying to the clipboard (by pressing
Ctrl+C twice) then format all cells as text and paste back from clipboard.. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
I run into that problem so rarely that I can't really remember what causes it.
Is the exported Excel file in an old version of Excel? For example, when you save it, does Excel ask if you want to upgrade the file from version 2.0? If yes, does saving it as a current version help? OR What happens if you copy the cells into a new workbook? Same issue? (obviously taking SWAGs here) <g *********** Regards, Ron XL2002, WinXP "Erinayn" wrote: It displays the words but it is formated as number type fields not text type fields. If you go into FormatCell all of the text type formatting choices show ################### but the numeric type choices are fine. We then use the date from the Excel spreadsheet and mail merge it into a word document - which is not picking up those fields now. Also, an interesting twist when you do a filter on the column - it doesn't recognize it as anything - when you choose "Blanks" from the filter dropdown it brings up the true blanks plus those fields. "Ron Coderre" wrote: Are you saying that the same Cognos-to-Excel report displayed the field as text pre-SP2, but displays numbers now? Example: Was: 0032 Now: 32 OR...are you importing a Cognos gererated text file and that's the new behavior? *********** Regards, Ron XL2002, WinXP "Erinayn" wrote: Anyone have an answer about the other way. We run a report from a Cognos report writing and it dumps to excel. We recently had the Office SP2 installed and now some of our test data fields are showing a numbers only. We've tried everything to get them to change to text. Paste special, formatting, format paintbrush, adding apostrophe, removing the date - all kinds of different things. Does anyone have an idea of a solution or a cause? "Gary''s Student" wrote: Ron's solution is best, but if you really need a macro then: Sub numerify() Dim r As Range Count = 0 For Each r In Selection 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 Converts both text cells and apostrophe'd values. -- Gary's Student "Donna S" wrote: I have a column of numbers but some is stored as text and some as numbers. I would like to convert all of them to numbers in a macro but don't know how. I've tried recoreding a macro when selecting the list and clicking on the convert to number but the macro doesn't seam to record that part. Help please. Donna |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert text to number
I tried a regular paste and a paste special - values into a new one and it
didn't work. I tried to do the clipboard but it wasn't like in word so I wasn't sure what to do with that. "Ron Coderre" wrote: I run into that problem so rarely that I can't really remember what causes it. Is the exported Excel file in an old version of Excel? For example, when you save it, does Excel ask if you want to upgrade the file from version 2.0? If yes, does saving it as a current version help? OR What happens if you copy the cells into a new workbook? Same issue? (obviously taking SWAGs here) <g *********** Regards, Ron XL2002, WinXP "Erinayn" wrote: It displays the words but it is formated as number type fields not text type fields. If you go into FormatCell all of the text type formatting choices show ################### but the numeric type choices are fine. We then use the date from the Excel spreadsheet and mail merge it into a word document - which is not picking up those fields now. Also, an interesting twist when you do a filter on the column - it doesn't recognize it as anything - when you choose "Blanks" from the filter dropdown it brings up the true blanks plus those fields. "Ron Coderre" wrote: Are you saying that the same Cognos-to-Excel report displayed the field as text pre-SP2, but displays numbers now? Example: Was: 0032 Now: 32 OR...are you importing a Cognos gererated text file and that's the new behavior? *********** Regards, Ron XL2002, WinXP "Erinayn" wrote: Anyone have an answer about the other way. We run a report from a Cognos report writing and it dumps to excel. We recently had the Office SP2 installed and now some of our test data fields are showing a numbers only. We've tried everything to get them to change to text. Paste special, formatting, format paintbrush, adding apostrophe, removing the date - all kinds of different things. Does anyone have an idea of a solution or a cause? "Gary''s Student" wrote: Ron's solution is best, but if you really need a macro then: Sub numerify() Dim r As Range Count = 0 For Each r In Selection 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 Converts both text cells and apostrophe'd values. -- Gary's Student "Donna S" wrote: I have a column of numbers but some is stored as text and some as numbers. I would like to convert all of them to numbers in a macro but don't know how. I've tried recoreding a macro when selecting the list and clicking on the convert to number but the macro doesn't seam to record that part. Help please. Donna |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text strings to a code or number | Excel Worksheet Functions | |||
Convert from number to text | Excel Worksheet Functions | |||
how can I convert numeric number into text | Excel Discussion (Misc queries) | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
How do I convert a number formated as a date to text in Excel? | Excel Discussion (Misc queries) |