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
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
----------------------------------------
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
Use the "Center Across Selection" option instead.
RE: Combining cells
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 Subthis 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
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
onlyadrafter's second post looks like a reasonable starting point.
RE: Combining cells
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
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 SubRE: Combining cells
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 SubRE: Combining cells
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
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
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
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 SubRE: Combining cells
----------------------------------------
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