Convert numbers stored as text to numbers

[Shay A]

Hello,
A direction to such a code would wonderful. I searched this forum prior to asking, just wasn't sure I found what I was looking for...

Thanks!

 

[Hui]

Is this a followup to another post?

If so please ask the question there

If not, Can you please ask the question here?

 

[Shay A]

Hi,
it is not a follow up, I am looking for a code...

 

[Hui]

You need to better explain your situation

If you have a column of Text that is numbers simply multiplying the values by 1 will convert them to numbers

Can you post a sample file ?

 

[Shay A]

I am familiar with this method, looking for a macro to do it automatically. I have many files with this issue.

TY

 

[Manish Agrawal]

Mr. hui,

Can you help me for the same,
i want condition formula.
I m not able to make this formula, error showing many argument use.

Thanks
Manish Kumar Gupta

 

[jindon]

One way

Sub test()
    With Columns(1)
        .NumberFormat = ""
        .TextToColumns
    End With
End Sub

 

[Shay A]

Thanks, it seems to be working find. What if I have a varying number of rows and columns?

 

[jindon]

Depends.
If no formula used.

Sub test()
    With ActiveSheet.UsedRange
        .NumberFormat = ""
        .Value = Evaluate("if(isnumber(" & .Address & ")," & .Address & "*1,if(" & .Address & "<>""""," & .Address & ",""""))")
    End With
End Sub

Otherwise you need to loop.

 

[Shay A]

No formula in this case.

Thanks

 

[Shay A]

BTW, what's the difference between.currentrange and.usedrange?

 

[NARAYANK991]

View attachment 33798

Mr. hui,

Can you help me for the same,
i want condition formula.
I m not able to make this formula, error showing many argument use.

Thanks
Manish Kumar Gupta

Hi ,

This question has nothing to do with this thread ; can you please post this question in a new thread of your own ?

If possible , give more details of what your data is like , and what you would like to do , and upload a workbook with adequate real-life data.

Narayan

 

[jindon]

BTW, what's the difference between.currentrange and.usedrange?

Explain what is "currentrange"???

 

[Shay A]

Oops I mean.currentregion

 

[jindon]

Both in vb help.

 

[Shay A]

Which is triggered how?

 

[Shay A]

I mean, after I serach for that on the Object Browser, is there a following explaination?

TY

 

[Shay A]

Hello,
how do I change this code so it converts numbers stored as numbers to numbers stored as text?

Sub test()
With Columns(1)
.NumberFormat = ""
.TextToColumns
End With
End Sub


Thanks!

 

[NARAYANK991]

Hi ,

Change the following line as shown :

.NumberFormat = "@"

Narayan

 

[Shay A]

and is it possible to include ranges (named ranges) from different sheets?

TY

 

[NARAYANK991]

and is it possible to include ranges (named ranges) from different sheets?

TY

Hi ,

Please explain.

Narayan

 

[Shay A]

Hi,
inside the WITH I want to include fw anges
TY

 

[NARAYANK991]

Hi ,

The With specifies only column A ; if you want multiple ranges to be covered in this macro , please specify all of them in this thread , or even better , upload your workbook with the data in it. The macro will have to be revised , since these multiple ranges cannot be covered in the With clause.

Narayan

 

[Shay A]

Hi,
further to the lovely code you provided me, is there a way to create a loop to convert all numbers stored as text to numbers with the format
.NumberFormat = ""
However, if there are already numbers on the sheet that are stored as numbers, the code will not change their formatting.

Thanks again,
Shay

 

[Shay A]

Hi ,

Change the following line as shown :

.NumberFormat = "@"

Narayan

Hi,
that doesn't seem to work, should it cinvert numbers to text?

TY