Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    196
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Text to Columns Annoyance - Excel 07

    Hi,
    I have an annoyance with Text to Columns - it interferes with my normal Pasting of data.

    Once a week I use Text to Columns to import data, and I put a tick in the space box as I want the separation into cells to be done by spaces. This works fine, but when I subsequently Paste any other data in Excel the space box is still ticked and my data is separated out by the spaces as if I was using Text to Columns.

    I have to do a separate dummy Text to Columns operation to remove the tick in the space box to Paste my data correctly.

    How can I avoid this situation?

    I tried recording a macro for the dummy Text to Columns, but because I cancel it after the Tick is removed from the space box, there is no code produced to modify or use!

    Any suggestions gratefully received.

    Peter Moran

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,531
    Thanks
    213
    Thanked 859 Times in 790 Posts
    Peter,

    Try this code:
    Code:
    Sub CLRTextToColDelims()
        Selection.TextToColumns Destination:=Range("A1"), _
           DataType:=xlDelimited, _
           TextQualifier:=xlDoubleQuote, _
           ConsecutiveDelimiter:=False, _
           Tab:=False, _
           Semicolon:=False, _
           Comma:=False, _
           Space:=False, _
           Other:=False, _
           FieldInfo:=Array(1, 1), _
           TrailingMinusNumbers:=True
    End Sub
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    petermoran (2014-07-31)

  4. #3
    Platinum Lounger
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    3,896
    Thanks
    7
    Thanked 259 Times in 244 Posts
    'Tis a pity you have to resort to scripting it when a menu option would have been simpler.

    cheers, Paul

  5. #4
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    196
    Thanks
    5
    Thanked 0 Times in 0 Posts
    Hi,

    Works perfectly!

    Many thanks RetiredGeek.

    Thanks to you the Forces of good computing are obviously with me!

    Peter Moran

Posting Permissions

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