+ Reply to Thread
Results 1 to 9 of 9

Text to column using VBA

  1. #1
    Registered User
    Join Date
    01-16-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    68

    Text to column using VBA

    Hi All,

    Need vba code to convert 'Mod' sheet from text to column in 'Row Data' sheet by click of 'Text to Column' button.
    The data selected in 'Mod' sheet should be converted to column done manually by clicking 'Text to Column' -> 'Delimited' -> 'Space'

    Sheet attached.

    Sheet.xlsm


    Thanks in Advance
    Regards,
    AAK.
    Last edited by aakhan2011; 04-19-2014 at 09:35 AM. Reason: question solved

  2. #2
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Text to column using VBA

    you can use the macro recorder
    If solved remember to mark Thread as solved

  3. #3
    Registered User
    Join Date
    01-16-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Text to column using VBA

    I dont want to use macro recorder, i want only vba . Expects ur nelp needed.

  4. #4
    Forum Expert
    Join Date
    07-15-2012
    Location
    Leghorn, Italy
    MS-Off Ver
    Excel 2010
    Posts
    3,431

    Re: Text to column using VBA

    the macro recorder records a VBA macro, as you want. Do you prefer I use recorder and send you the macro ?

  5. #5
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Text to column using VBA

    Hi, patel45,

    Do you prefer I use recorder and send you the macro ?
    I´d love to learn from an expert and see your macro code for using TextToColumns from Sheet "Mod" to Sheet "Row Data" directly, please.

    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Text to column using VBA

    Hi, aakhan2011,

    maybe this could be a way to go:
    Please Login or Register  to view this content.
    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
    Ciao,
    Holger

  7. #7
    Registered User
    Join Date
    01-16-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Text to column using VBA

    Good help @HaHoBe

    I got the code from the macro as below -

    Private Sub CommandButton1_Click()
    Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
    Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
    :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:= _
    True
    End Sub

    But i want it to be pasted to other sheet instead of same sheet . Please help .

    Sheet attached below to show how it looks on clicking the button in "Row Data" worksheet


    Sheet.xlsm

    Regards,
    A.A.K.

  8. #8
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Text to column using VBA

    Hi, aakhan2011,

    please have a read at the Forum Rules and wrap your procedure here with code-tags.

    I´m sorry, to my limited knowledge it´s not possible to use TextToColumns and get the result over to a different sheet. I tested with Excel2007 and Excel2013 and TextToColumns referred to the same sheet no matter how I qualified the target range.

    You may be using TextToColumns to split the data on the very sheet maybe starting at F1, then move (cut & paste) the columns to the wanted sheet.

    Ciao,
    Holger

  9. #9
    Registered User
    Join Date
    01-16-2014
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    68

    Re: Text to column using VBA

    Quote Originally Posted by HaHoBe View Post
    Hi, aakhan2011,

    please have a read at the Forum Rules and wrap your procedure here with code-tags.

    I´m sorry, to my limited knowledge it´s not possible to use TextToColumns and get the result over to a different sheet. I tested with Excel2007 and Excel2013 and TextToColumns referred to the same sheet no matter how I qualified the target range.

    You may be using TextToColumns to split the data on the very sheet maybe starting at F1, then move (cut & paste) the columns to the wanted sheet.

    Ciao,
    Holger
    Thanks a lot HaHoBe . Marking it as solved.

+ 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] Copy text in column 2 from column 1. Repeat text is displayed once only vba
    By KK1234 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-12-2014, 03:39 AM
  2. Text&number based column to auto display new text in new column
    By TCF in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-24-2014, 01:56 PM
  3. Replies: 12
    Last Post: 01-08-2014, 11:28 AM
  4. Trying to write amacro to compare text in column A to garbage text in column B
    By Big Dawg Dad in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2013, 03:31 PM
  5. Replies: 1
    Last Post: 09-01-2011, 12:05 AM

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