Text to Column Question

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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.

Reply | Quote

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.

35831-RightClick

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

Reply | Quote

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

35836-RightClick1 35837-RightClick2 35838-RightClick3

Code in standard module:

Code:

Public Sub PasteCombined()
Application.ScreenUpdating = False
[COLOR=”#008000″]’———————————————-
‘SET VARIABLES[/COLOR]
   num = ActiveCell.Address
   col = ActiveCell.Column
   Row = ActiveCell.Row
   pasterow = Row
   ActiveSheet.Paste
   Lastrow = Range(num).End(xlDown).Row
[COLOR=”#008000″]’———————————————
‘COMPOSE LINE, PLACE IN NEXT ROW[/COLOR]
   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
[COLOR=”#008000″]’———————————————
‘REMOVE LEFT OVER ROWS[/COLOR]
    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

Reply | Quote