Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2008
    Location
    San Diego, California, USA
    Posts
    73
    Thanks
    25
    Thanked 0 Times in 0 Posts

    Text to Column Question

    I have a mailing list in a Word table format that I want to convert to an Excel spreadsheet and then use the Text to Column feature.

    In the Word table complete addresses appear in a single cell. When I copy/paste the table to Excel the address appears in two cells. If I select source formatting when I paste the single cell becomes two cells but the table retains it original appearance otherwise. If I select destination format I get two cells.

    Smith Retail Store
    123 Any Street, Anywhere, CA 12345

    In Word table, this is a single cell address. In Excel it becomes cells A1 and A2. I need the address to remain in a single cell for Text to Column to work.

    Is there a simple way do this? Iím an Excel rookie so simple has to be kiddie pool level. Itís ok to tell me Iím out of luck.

    Running W8 and Office 2013 Professional

    Thanks
    plumber

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Are they "wrapped" in the word cell with something like a CarriageReturn or LineFeed? If you remove them in word (using find/replace), they should not cause the values to go tinto separate cells. Perhaps replacing it with a comma, would allow the text to column to work better as you could separate on the comma to get address, city state/zip [you would have to separate the state/zip in a 2nd pass using the space...

    Steve

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

    plumber53 (2013-12-19)

  4. #3
    Star Lounger
    Join Date
    Jul 2008
    Location
    San Diego, California, USA
    Posts
    73
    Thanks
    25
    Thanked 0 Times in 0 Posts
    Hey Steve,
    My work around is to cut/paste cell A2 into B1 and then run Text to Column.

    The Word table is from an older version of Word and may also have been designed on Apple computer.
    Now I've tried to set up a word table on my machine and if I lock the column widths I get an automatic carriage return effect when I come to the cell edge but this does not transfer to extra cells in Excel. The next thing I tried was to type within the Word table cell and then hit enter when I wanted the second line of the address to begin. This did not produce extra cells in Excel either.

    I checked the properties of the Word table column and text wrap is set for none.

    Does Word have a clear formats button somewhere that I haven't found?

    Kevin

    I don't know if old and new versions of word and excel just don't play well together or if Apple enters into this or it's a little of both.

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Plumber

    The problem you are experiencing in the embedded carriage return between the 2 lines of the address. When pastedThe following macros will take both lines and put them into one cell simply by just right clicking the cell.

    The first macro will enter the lines on one line in the cell. The second macro will insert an Alt-enter and stack the 2 lines in the cell. Place the macro in the sheet's module and delete it when you are finished.

    RightClick.png

    Macro 1: one line
    Code:
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Application.ScreenUpdating = False
       ActiveSheet.Paste
       text1 = Target.Value
       Target.Value = text1 & ", " & Target.Offset(1, 0).Value
       Target.Offset(1, 0).Value = ""
       Target.Select
    Application.ScreenUpdating = True
    End Sub
    Macro 2: multiple lines
    Code:
    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Cancel = True
    Application.ScreenUpdating = False
       ActiveSheet.Paste
       text1 = Target.Value
       Target.Value = text1 & Chr(10) & Target.Offset(1, 0).Value
       Target.Offset(1, 0).Value = ""
       Target.Select
    Application.ScreenUpdating = True
    End Sub
    Note: This works for copy/pasting one address at a time. I will work on a multiple copy/paste.

    HTH,
    Maud

  6. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 648 Times in 591 Posts
    Plumber,

    Here is something a little different. In the Excel right click context menu, I have added a macro called "PasteCombined" that when selected will paste an unlimited number of addresses copied from Word into Excel at the selected cell down (see images). The PasteCombined gets placed in a standard module. The code to create the right Context menu gets placed in the workbook module.

    HTH,
    Maud

    <-----------Word------------><-Right Context Menu in Excel-><-Pasted address in Excel->
    RightClick1.png RightClick2.png RightClick3.png

    Code in standard module:
    Code:
    Public Sub PasteCombined()
    Application.ScreenUpdating = False
    '----------------------------------------------
    'SET VARIABLES
       num = ActiveCell.Address
       col = ActiveCell.Column
       Row = ActiveCell.Row
       pasterow = Row
       ActiveSheet.Paste
       Lastrow = Range(num).End(xlDown).Row
    '---------------------------------------------
    'COMPOSE LINE, PLACE IN NEXT ROW
       For I = Row To Lastrow Step 2
            Cells(pasterow, col) = Cells(I, col) & Chr(10) & Cells(I, col).Offset(1, 0).Value
            pasterow = pasterow + 1
            If I + 1 >= Lastrow Then Exit For
        Next I
    '---------------------------------------------
    'REMOVE LEFT OVER ROWS
        For J = pasterow To Lastrow
            Cells(J, col) = ""
        Next J
       Range(num).Select
    Application.ScreenUpdating = True
    End Sub
    Code in ThisWorkbook Module: (http://www.ozgrid.com/VBA/right-click.htm)
    Code:
    Private Sub Workbook_Deactivate()
        On Error Resume Next
                With Application
                    .CommandBars("Cell").Controls("PasteCombined").Delete
                End With
        On Error GoTo 0
    End Sub
    
    Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
    Dim cBut As CommandBarButton
        On Error Resume Next
            With Application
                .CommandBars("Cell").Controls("PasteCombined").Delete
                Set cBut = .CommandBars("Cell").Controls.Add(Temporary:=True)
            End With
            With cBut
               .Caption = "PasteCombined"
               .Style = msoButtonCaption
               .OnAction = "PasteCombined"
            End With
        On Error GoTo 0
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2013-12-21 at 10:44.

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    Lexi (2014-02-08)

Posting Permissions

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