Converting a range values from number to text with vba

[mahhdy]

Hello,

I have a huge list, I want to change my reference columns data from number to text. I don't want to add any new column and for instance, use text() function. I am just wondering if it is possible to do that in place with a VBA code or not. I tried

selection=selection.text

and converting to array back and forth but not succeded. Any idea? I also don't want to do a loop as I need to this effort for several files which I can't manipulate their structure.

Regards,
M

 

[Rick Rothstein]

I have a huge list, I want to change my reference columns data from number to text.

I am not 100% sure what you are trying to do here, but does this give you the result you are looking for...

Selection.NumberFormat = "@"

 

[Peter_SSs]

If Rick's suggestion is not exactly what you want, there is also this to try, which produces a slightly different result, changing the values so that they report as 'Number Stored as Text"

Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 2)

 

[Rick Rothstein]

If Rick's suggestion is not exactly what you want, there is also this to try, which produces a slightly different result, changing the values so that they report as 'Number Stored as Text"

Selection.TextToColumns DataType:=xlDelimited, FieldInfo:=Array(1, 2)

And there is even one more one-liner method to do this...

Selection = Evaluate(Replace("IF(@="""","""",TEXT(@,""'0""))", "@", Selection.Address))

 

[Peter_SSs]

And there is even one more one-liner method to do this...

Selection = Evaluate(Replace("IF(@="""","""",TEXT(@,""'0""))", "@", Selection.Address))

Just noting that, apart from being very much slower (~6x), this does not do the same job as the code from post #3 . That is, perhaps, unless the original numbers are all integers and all non-negative

 

[Rick Rothstein]

Just noting that, apart from being very much slower (~6x), this does not do the same job as the code from post #3 . That is, perhaps, unless the original numbers are all integers and all non-negative

I wasn't worried about speed because I wasn't (and still aren't) sure in what way the OP wanted to make his numbers into text. Adding an apostrophe in front of the number is a way to do it and possibly a way the OP had in mind (not likely, but possibly), so I thought I would offer it "just in case". The problem with non-integers was a bad assumption on my part that we were dealing with integer numbers. If not, I am not sure my suggestion is easily fixable (and I'm not going to think about it until the OP gives us some more clarification). The problem with negative values was an oversight on my part and easily fixed. So if we are, in fact, talking about integers only, this is what my code line should have looked like...

Selection = Evaluate(Replace("IF(@="""","""",TEXT(@,""'0;'-0""))", "@", Selection.Address))

 

[Peter_SSs]

If not, I am not sure my suggestion is easily fixable ..

It is very easily fixed ..

Selection = Evaluate(Replace("IF(@="""","""",""'""&@)", "@", Selection.Address))

.. but unless there is some other advantage I don't see any reason to change from the shorter, faster, native vba built-in method designed to do exactly this sort of thing (post #3 ).

Having said that, it may well be that your suggestion from post #2 is all that the OP wants. I was guessing that the change may have been to easily allow, say, a VLOOKUP that required a text value as the lookup value and that is why I offered my alternative.

 

[mahhdy]

Hello,

Thanks a lot, Peters code was what I was looking for. I have a daily updating huge list, need to be compared and check with other files. My unique value for linking data is a number. I developed an add-in for myself which can answer lots of free filtering needs. But that filtering tool doesn't work on number fields! I am not sure why and asked several times, but anyway, the only solution would be quickly changing the numbers as text and using my functionality on that.

Hope this works. I checked it, now it works perfectly.

Peter, I am using this coll add-in instead of doing vlookup as I can control the excel behaviour in VBA while running code in big tables. The only big weakness of that add-in is it just prepared for excel tables (list objects) which I love to only work with them.

Here is my final code:

Sub NumbtoText()

If Not ActiveSheet.FilterMode Then Selection.TextToColumns _
                                    DataType:=xlDelimited, _
                                    FieldInfo:=Array(1, 2)
End Sub

That post is here:
https://www.mrexcel.com/forum/excel-questions/1023333-problem-filtering-numeric-fields-vba-code.html

and a link to that add-in:
https://www.mrexcel.com/forum/redir...://1drv.ms/x/s!ArGi1KRQ5iItgawugfTDT8vB6NxoCg

 

[mahhdy]

Rick,

You answered me on this question as well:
https://www.mrexcel.com/forum/excel-questions/1023517-manually-update-formula-table-using-vba.html

I was looking for "evaluate" function, I simply forgot that.

Thanks a lot

I wasn't worried about speed because I wasn't (and still aren't) sure in what way the OP wanted to make his numbers into text. Adding an apostrophe in front of the number is a way to do it and possibly a way the OP had in mind (not likely, but possibly), so I thought I would offer it "just in case". The problem with non-integers was a bad assumption on my part that we were dealing with integer numbers. If not, I am not sure my suggestion is easily fixable (and I'm not going to think about it until the OP gives us some more clarification). The problem with negative values was an oversight on my part and easily fixed. So if we are, in fact, talking about integers only, this is what my code line should have looked like...

Selection = Evaluate(Replace("IF(@="""","""",TEXT(@,""'0;'-0""))", "@", Selection.Address))

 

[Peter_SSs]

Thanks a lot

You're welcome.