Combining cells 3

[dgallup]

I frequently have to import an unstructured text file into excel and no combination of delimiters can properly get the cells filled in properly. What usually happens is there is a description field that has a random number of words that get put into multiple cells in a row. What I would like to be able to do is select these cells (2, 3, 4 or more) and combine the contents of all of them into the left most cell with spaces inserted between the contents of the individual cell. Then the extra cells need to be deleted and shifted left. I don't have the skill to do this, hope you genius's can give me some ideas.

----------------------------------------

The Help for this program was created in Windows Help format, which depends on a feature that isn't included in this version of Windows.

 

[onlyadrafter]

Hello,

does this code work as expected?

Sub COMBINE_CELLS()
    For MY_ROWS = 1 To Range("A" & Rows.Count).End(xlUp).Row
        MY_TEXT = Cells(MY_ROWS, 1).Value
        For MY_COLS = 2 To Cells(MY_ROWS, Columns.Count).End(xlToLeft).Column
            If Not IsEmpty(Cells(MY_ROWS, MY_COLS).Value) Then
                MY_TEXT = MY_TEXT & " " & Cells(MY_ROWS, MY_COLS)
            End If
        Next MY_COLS
        Cells(MY_ROWS, 1).Value = MY_TEXT
        Range(Cells(MY_ROWS, 2), Cells(MY_ROWS, MY_COLS)).ClearContents
    Next MY_ROWS
End Sub

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

 

[dgallup]

Close. That combined every cell on a line into one cell and did it for every line. I want to just want to pre-select several contiguous cells in a row and combine them. On one line it may be 6 cells and on the next line it may be only 2. No way to know at import because it's unstructured text, no tabs or commas separating fields.

----------------------------------------

The Help for this program was created in Windows Help format, which depends on a feature that isn't included in this version of Windows.

 

[MintJulep]

Never combine cells like this. It mucks up your ability to filter, sort and do all sorts of other useful things. Even if you think you don't need to do them now, you or someone else in the future will.

Use the "Center Across Selection" option instead.

 

[onlyadrafter]

Hello,

how about

Sub COMBINE_SELECTED_CELLS()
    For Each CELL In Selection
        MY_TEXT = MY_TEXT & " " & CELL.Value
    Next CELL
    Selection.ClearContents
    ActiveCell.Value = MY_TEXT
End Sub

this assumes you select cells from left to right i.e. A1 across to D1.

Will you only select cells from only 1 row at a time?

----------------------------------
Hope this helps.
----------------------------------

been away for quite a while
but am now back

 

[pmover]

try the concatenate function.
it has the ability to combine text from many cells into 1 cell. since there are an unknown number of columns, make the formula to combine sufficient cells greater than the number of columns.
once this is done, i used the copy and paste values as this allows me to work with the combine string without formulas.
good luck.
-pmover

 

[MintJulep]

Sorry, I misunderstood what you are trying to accomplish.

onlyadrafter's second post looks like a reasonable starting point.

 

[dgallup]

onlyadrafter - That is combining the contents of the selected area into the left hand cell beautifully. Only other thing I would like it to do is delete the now empty cells and shift left. So if I select 4 cells, it combines all the contents into the left cell (which the macro is doing now) then deletes the 3 cells to the right.

I only want to work on one row at a time as the number of cells to combine changes with every row.

----------------------------------------

The Help for this program was created in Windows Help format, which depends on a feature that isn't included in this version of Windows.

 

[MintJulep]

Try this mod to onlyadrafter's code:

Sub COMBINE_SELECTED_CELLS()
    For Each CELL In Selection
        MY_TEXT = MY_TEXT & " " & CELL.Value
     [COLOR=#EF2929]CELL.value = ""[/color]
    Next CELL
    Selection.ClearContents
    ActiveCell.Value = MY_TEXT
End Sub

 

[MintJulep]

This is a little more structured:

Public Sub Combine()
Dim MyRange As Range
Dim LeftCell As Range
Dim MyText As String

Set MyRange = Selection
Set LeftCell = Range(MyRange.Columns(1).Address)

For Each Cell In MyRange
    MyText = MyText & " " & Trim(Cell.Value)
    Cell.Clear
Next Cell

LeftCell.Value = MyText

End Sub

 

[dgallup]

Two things:

1) I'm getting a space character at the beginning of the combined cell.
2) Need to delete the now empty cells.

Thanks for the help, this is saving me much cutting and pasting already.

----------------------------------------

The Help for this program was created in Windows Help format, which depends on a feature that isn't included in this version of Windows.

 

[MintJulep]

1) Change: LeftCell.Value = MyText to LeftCell.Value = Trim(MyText)

2) You can delete the contents of a cell. You can delete a row. You can delete a column. You can't delete a cell.

 

[dgallup]

Trim works, thanks.

I don't understand "You Can't delete a cell."

I can select a range of cells, RMB and select delete. Then I get a prompt to select one of 4 options:

[ul]
[li]Shift cells left[/li]

[li]Shift cells up[/li]

[li]Entire row[/li]

[li]Entire column[/li]

[/ul]

I want to shift the cells left.

----------------------------------------

The Help for this program was created in Windows Help format, which depends on a feature that isn't included in this version of Windows.

 

[MintJulep]

Ok, you can delete cells.

Public Sub Combine_Shift_Left()
Dim MyRange As Range
Dim LeftCell As Range
Dim RightCell As Range
Dim KillRange As Range
Dim MyText As String

Set MyRange = Selection
Set LeftCell = Range(MyRange.Columns(1).Address)
Set RightCell = Range(MyRange.Columns(MyRange.Columns.Count).Address)
Set KillRange = Range(MyRange.Columns(2).Address, RightCell)


For Each Cell In MyRange
    MyText = MyText & " " & Trim(Cell.Value)
    Cell.Clear
Next Cell

LeftCell.Value = Trim(MyText)
KillRange.Delete Shift:=xlToLeft

End Sub

 

[dgallup]

Thanks a million, that's exactly what I need. Looks like I should learn VBA!

----------------------------------------

The Help for this program was created in Windows Help format, which depends on a feature that isn't included in this version of Windows.

 

[MintJulep]

Credit is due to onlyadrafter for putting things on the right path.