Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text to number
Hi all,
when I copy and paste data to my worksheet, the numbers appear to be formatted as text. I can easily convert these to numbers by either selecting the error message and converting or using the special paste method and so on. However, my spreadsheet requires for data to be copied and pasted continuously and I dont want to have to convert the text to numbers every time. Is there a way this could be automated with some code? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text to number
Sub numerify()
For Each r In ActiveSheet.UsedRange If r.NumberFormat = "@" And IsNumeric(r.Value) Then r.NumberFormat = "General" r.Value = r.Value End If Next End Sub -- Gary''s Student - gsnu200797 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text to number
I have the problem as the original posted. I have copied the code exactly as
written. Nothing happens when I execute the macro. Am I dense? Does "r" need to be defined. I tried selecting a range and then run the macro and it did not work that way either. I just checked the, cells are already formatted as general. I need to convert to "Convert to Number". I can do this when I click on the exclamation point but I want to incorporate this within all of the other stuff I have written. -- Thanks - K "Gary''s Student" wrote: Sub numerify() For Each r In ActiveSheet.UsedRange If r.NumberFormat = "@" And IsNumeric(r.Value) Then r.NumberFormat = "General" r.Value = r.Value End If Next End Sub -- Gary''s Student - gsnu200797 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text to number
Kristen
I find the macro will not work if the "numbers" are preceded by an apostrophe or contain extraneous <space(s) and not just formatted as Text. You could Dim r as Range but that won't help if the case is as I state above. Without a macro, Format all to General. Copy an empty cell. Select the range to change and EditPaste SpecialAddOKEsc. Gord Dibben MS Excel MVP On Thu, 14 Aug 2008 12:48:21 -0700, Kristen wrote: I have the problem as the original posted. I have copied the code exactly as written. Nothing happens when I execute the macro. Am I dense? Does "r" need to be defined. I tried selecting a range and then run the macro and it did not work that way either. I just checked the, cells are already formatted as general. I need to convert to "Convert to Number". I can do this when I click on the exclamation point but I want to incorporate this within all of the other stuff I have written. -- Thanks - K "Gary''s Student" wrote: Sub numerify() For Each r In ActiveSheet.UsedRange If r.NumberFormat = "@" And IsNumeric(r.Value) Then r.NumberFormat = "General" r.Value = r.Value End If Next End Sub -- Gary''s Student - gsnu200797 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Convert text to number
Hi Gord,
I have a similar issue. The "number" is preceded by an apostrophe. I am using VLOOKUP of a number. ie. have existing '12345 need to lookup 12345 to return another value. I tried the Paste Special function though nothing happened. Thanks for your help. "Gord Dibben" wrote: Kristen I find the macro will not work if the "numbers" are preceded by an apostrophe or contain extraneous <space(s) and not just formatted as Text. You could Dim r as Range but that won't help if the case is as I state above. Without a macro, Format all to General. Copy an empty cell. Select the range to change and EditPaste SpecialAddOKEsc. Gord Dibben MS Excel MVP On Thu, 14 Aug 2008 12:48:21 -0700, Kristen wrote: I have the problem as the original posted. I have copied the code exactly as written. Nothing happens when I execute the macro. Am I dense? Does "r" need to be defined. I tried selecting a range and then run the macro and it did not work that way either. I just checked the, cells are already formatted as general. I need to convert to "Convert to Number". I can do this when I click on the exclamation point but I want to incorporate this within all of the other stuff I have written. -- Thanks - K "Gary''s Student" wrote: Sub numerify() For Each r In ActiveSheet.UsedRange If r.NumberFormat = "@" And IsNumeric(r.Value) Then r.NumberFormat = "General" r.Value = r.Value End If Next End Sub -- Gary''s Student - gsnu200797 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text number to number formate | Excel Discussion (Misc queries) | |||
Convert a number formatted as text to a number in a macro | Excel Programming | |||
convert text-format number to number in excel 2000%3f | Excel Discussion (Misc queries) | |||
not able to convert text, or graphic number to regular number in e | Excel Worksheet Functions | |||
Convert number in text format to number | Excel Programming |