INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Combining cells3

## 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,

#### 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

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

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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!