INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Combining cells

Combining cells

(OP)
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.

RE: Combining cells

Hello,

does this code work as expected?

CODE

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

RE: Combining cells

(OP)
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.

RE: Combining cells

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.

RE: Combining cells

2
Hello,

how about

CODE

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

RE: Combining cells

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

RE: Combining cells

Sorry, I misunderstood what you are trying to accomplish.

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

RE: Combining cells

(OP)
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.

RE: Combining cells

Try this mod to onlyadrafter's code:

CODE --> VBA

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

RE: Combining cells

This is a little more structured:

CODE --> VBA

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 

RE: Combining cells

(OP)
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.

RE: Combining cells

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.

RE: Combining cells

(OP)
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:

  • Shift cells left
  • Shift cells up
  • Entire row
  • Entire column
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.

RE: Combining cells

Ok, you can delete cells.

CODE --> VBA

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 

RE: Combining cells

(OP)
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.

RE: Combining cells

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close