+ Reply to Thread
Results 1 to 5 of 5

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

  1. #1
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    234

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

    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.

  2. #2
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,024

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

    The solution was already provided in your other post...
    Please Login or Register  to view this content.
    Good Luck...
    I don't presume to know what I am doing, however, just like you, I too started somewhere...
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
    Also....Add a comment if you like!!!!
    And remember...Mark Thread as Solved...
    Excel Forum Rocks!!!

  3. #3
    Registered User
    Join Date
    01-08-2019
    Location
    Chennai, India
    MS-Off Ver
    2010
    Posts
    35

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

    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

  4. #4
    Forum Contributor
    Join Date
    11-17-2018
    Location
    Los Angeles
    MS-Off Ver
    Office 2010
    Posts
    234

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

    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.

  5. #5
    Forum Guru Sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2019 | 2021
    Posts
    15,024

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

    Apologies...Misunderstood...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Split text to columns loosing "0" in numbers
    By soli004 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-15-2014, 11:03 AM
  2. convert hindi text like as "jkˇph" into "Ranchi" english text
    By dahiya@123 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-04-2013, 01:30 AM
  3. [SOLVED] Use VBA to run "text to columns' Loop on first row to convert from text to date format
    By csmiley in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 06-03-2013, 02:06 PM
  4. [SOLVED] Separating Binary Numbers via "Text to Columns" Type Approach
    By JP777 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-22-2012, 09:19 AM
  5. Problem with "Convert to Text Columns Wizard"
    By Maximus4587 in forum Excel General
    Replies: 3
    Last Post: 07-13-2010, 03:47 AM
  6. Replies: 3
    Last Post: 12-20-2007, 03:50 PM
  7. Replies: 2
    Last Post: 02-27-2005, 01:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1