Convert text to number

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

Ron This works. Thanks.

Donna

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

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

 

[Guest]

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?

 

[Guest]

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

 

[Guest]

It displays the words but it is formated as number type fields not text type
fields.

If you go into Format>Cell 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.

 

[Lori]

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..

 

[Guest]

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

 

[Guest]

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.