CODEWITHSUNDEEP
vbaexcel
Mahhdy
Mahhdy

Reputation: 592

Directly converting a range of numeral entries to text with VBA

I have a huge list, which I have to work on each day. I want to change my reference columns data from number to text so it enables me to do some advanced filtering and referencing futures. I don't want to add any new column and for instance, use Text() function in excel as this range is same in different files I have to concatenate and link. I am just wondering if it is possible to do that in place with a VBA code or not. I tried:

selection=selection.text

or doing some paste special efforts with ', empty cells 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 or change them with my weak laptop each time every day!

Regards, M

Upvotes: 3

Views: 2613

Answers (3)

Mahhdy
Mahhdy

Reputation: 592

Based on @l42 answer and point here is what I was needed... and came to

Sub NumbtoText()

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

Thanks

Upvotes: 2

L42
L42

Reputation: 19727

You may want to use built-in Excel functionality Text-to-columns.
If you want it automated using VBA, try this:

With Sheet1
    .Range("B1", "B4").TextToColumns Destination:=.Range("B1"), _
                                     DataType:=xlDelimited, _
                                     FieldInfo:=Array(1, 2)
End With

Above converts all numbers in the range B1:B4 in sheet1.
The FieldInfo argument identifies the conversion.
We use Array(1,2) which means we want to convert column 1 to text.
Check Text to columns functionality to understand it further. HTH.

Upvotes: 4

DaBeau96
DaBeau96

Reputation: 548

This should help you out. It converts a a range of values into text format

Sub Convert_To_Text()
    Dim vData As Variant
    vData = Range("A1:D1")
    Range("A1:D1").NumberFormat = "@"
    Range("A1:D1") = vData
End Sub

Upvotes: 2

Related Questions