Excel 2010 text to numbers 1

[Andrzejek]

I have numbers formatted as text, I would like to convert them to numbers.

In Microsoft link convert-numbers-stored-as-text-to-numbers they show how to do it in:

Technique 1: Convert text-formatted numbers by using Error Checking

but all what I can see in code when I do step-by-step is:
[tt]
Range("A1:B4").Select
[/tt]
and all other steps are not recorded.

How can I convert numbers stored as text to numbers in code?

Have fun.

---- Andy

 

[SkipVought]

hi,

Enter the number 1 in an unused cell.

COPY that cell.

Select the range of text that you want to conver to numbers.

Edit > Paste Special -- MULTIPLY.

In other words, by multiplying each cells' text value by 1, you will coerce numeric results.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Thanks Skip, I did try that, but I end up with 0's in the cells that were empty before :-(

Have fun.

---- Andy

 

[SkipVought]

Why did you select EMPTY cells?

"Select the range of text that you want to conver to numbers."

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

My range of cell may (and will) include empty cells :-(

I found this piece of code that looks like it will work OK for me:

    For Each tcell In Selection.Cells
        If TypeName(tcell.Value) = "String" Then
            tcell.Value = Val(tcell.Value)
        End If
    Next

The only problem is, if I use [tt]Option Explicit[/tt], [tt]tcell[/tt] is not declared and I don't know what to declared it As ???

Have fun.

---- Andy

 

[SkipVought]

   dim tcell as range

   For Each tcell In Selection.Cells
      with tcell
        If TypeName(.Value) = "String" Then
            .Value = Val(.Value)
        End If
      end with
   Next

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Thank you Skip, that will work

And just to shed some light on my ‘spaces in range of numbers’ – users originally wanted numbers to be displayed as 25.0 or 12.500 or 10.00 and to be aligned by decimal point. Whole bunch of rows and in several columns, with some values missing (that’s where spaces are). That’s why the numbers are text. Now they are complaining: “We cannot sum it or anything…” and that’s why the simplest way for me to do was to give them another worksheet and convert them to numbers.

Have fun.

---- Andy

 

[xlbo]

your best bet when wanting to display numbers in a certain way is to use formatting - that doesn't change the underlying properties of the value in teh cell and therefore allows for calculation

In your suituation, I would simply format all cells to the maximum number of decimals i.e. if the max decimals is 4 then format as 0.0000

This will show all decimal points lined up and will add any extra 0s after the last decimal entered i.e. if you enter 10 in a cell it will show as 10.0000

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[combo]

In case of formatting, you can use "?" to align numbers to decimal point, for instance with custom format set to [tt]#.????[/tt].
The drawback is how to display variable number of trailing 0's, however replacing some "?" by "0" fixes the number of decimal digits displayed.

combo

 

[Andrzejek]

Thank you for the info.
User wanted to have data displayed (something like this):
[tt]
....1.00
..123.0
1,234.000
...12
....0.00
[/tt]
(I had to use . because spaces were gone in preview here :-( )

Have fun.

---- Andy

 

[SkipVought]

Did you look at the Worksheet_Change event method that I posted in the other forum?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

I use my code from 12 Jun 12 10:32 and your (Skip) suggestion from 12 Jun 12 10:37 and all is working OK now.

I am just answering some questions here, but the problem is solved as far as I know.

Thank you.

Have fun.

---- Andy