"Text to Columns" to convert numbers to text in-place using a button with VBA

[Luisftv]

Please help.

I have a button in a sheet that I would like to press and then it would run VBA code to convert a whole column (for example, column E) containing numbers to text and leaving them in the same column, in their original place.

The manual way is:

1. Select a range (this could be a whole row or the whole column or part of it)
2. Go to the DATA tab on the ribbon
3. Click on "Text to Column"
4. Choose "Delimited" and click Next
5. For Delimiters:
- uncheck all
- text qualifier: *
- then click Next
6. For Column data format select "Text" and for Destination leave the default which is the selected range
7. Click on Finish.

This manual way converts all the numbers in the column to text leaving them in place. I do not need them moved anywhere nor split. I need them to stay where they are, in the source column, simply converted to text using this "Text to Column" feature of Excel and looking the same way as before the conversion (if 1025632, then after conversion 1025632 but as text).

So, I need to automate this manual process by having a button that when pushed will do the whole thing in one go (using VBA). I'm hoping the solution will be "self-contained" so it will not interfere with other existing VBA code in the sheet.

See here for an example: https://www.automateexcel.com/vba/text-to-columns/

I just can't find a way to modify their code to work with a button which when pressed will be applied in-place, leaving them in the source column (or row, or selection).

I hope I made sense.

Thank you.

[Sintek]

The solution was already provided in your other post...

Please Login or Register  to view this content.

[Raj.xls]

Sub Number_to_text()

'
Columns("E:E").Select
Selection.TextToColumns Destination:=Range("E1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
End Sub

[Luisftv]

Hello Sintek. You mentioned that the solution to this request was already provided in the other thread, but maybe I forgot to state there that this solution adds an apostrophe at the beginning of the numbers. That will not do. It has to be left as they are but with the property set as Text.

Raj.xls, thank you for the solution. I tested it in the actual excel file where we need it (and a few others) and it works. It does exactly as needed and using the "Text to Column" Excel feature as stated in the request. Thank you for reading carefully the request.

Both of you, stay safe and have a blessed day.

[Sintek]

Apologies...Misunderstood...