Merge various cells value in one row into one cell

  • Thread starter Thread starter K
  • Start date Start date
K

K

Hi all, I have data in my sheet (see below)
A B C D E………col
xx 01 02 06 07
zz 55 90 22 78
tt 15 02 05 09
ss 66 58 02 04
aa 18 20 10 03

I want macro which should combine each row cells value from coloum B
to E and put result in coloum F something like this (see below)
A B C D E F……col
xx 01 02 06 07 01-02-06-07
zz 55 90 22 78 55-90-22-78
tt 15 02 05 09 15-02-05-09
ss 66 58 02 04 66-58-02-04
aa 18 20 10 03 18-20-10-03

macro should put result in column F down to last value cell in column
A and macro should make sure that those values in cells which have
zero on the start like 01 or 02 when they will get combine by macro
they should stay in same format like "01-02" instead of "1-2". I hope
I was able to explain my question. Can any friend can help me in this.
 
Try this
Sub putcolstogetherinonecell()
Columns("f").ClearContents
For i = 2 To Cells(Rows.Count, "a").End(xlUp).Row
ms = ""
lc = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 2 To lc
ms = ms & "-" & Cells(i, j)
Next j
Cells(i, lc + 1) = Right(ms, Len(ms) - 1)
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Hi all, I have data in my sheet (see below)
A B C D E………col
xx 01 02 06 07
zz 55 90 22 78
tt 15 02 05 09
ss 66 58 02 04
aa 18 20 10 03

I want macro which should combine each row cells value from coloum B
to E and put result in coloum F something like this (see below)
A B C D E F……col
xx 01 02 06 07 01-02-06-07
zz 55 90 22 78 55-90-22-78
tt 15 02 05 09 15-02-05-09
ss 66 58 02 04 66-58-02-04
aa 18 20 10 03 18-20-10-03

macro should put result in column F down to last value cell in column
A and macro should make sure that those values in cells which have
zero on the start like 01 or 02 when they will get combine by macro
they should stay in same format like "01-02" instead of "1-2". I hope
I was able to explain my question. Can any friend can help me in this.
 
Try this
Sub putcolstogetherinonecell()
Columns("f").ClearContents
For i = 2 To Cells(Rows.Count, "a").End(xlUp).Row
ms = ""
lc = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 2 To lc
ms = ms & "-" & Cells(i, j)
Next j
Cells(i, lc + 1) = Right(ms, Len(ms) - 1)
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

Hi all,  I have data in my sheet (see below)
    A        B        C       D       E………col
    xx       01      02      06       07
    zz       55      90      22       78
    tt        15       02      05       09
    ss       66      58      02       04
    aa       18      20      10       03

I want macro which should combine each row cells value from coloum B
to E and put result in coloum F something like this (see below)
    A        B          C        D        E             F……col
    xx       01        02       06       07      01-02-06-07
    zz       55        90       22       78      55-90-22-78
    tt        15        02       05      09       15-02-05-09
    ss       66        58       02       04      66-58-02-04
    aa       18        20       10       03      18-20-10-03

macro should put result in column F down to last value cell in column
A and macro should make sure that those values in cells which have
zero on the start like 01 or 02 when they will get combine by macro
they should stay in same format like "01-02" instead of "1-2".  I hope
I was able to explain my question. Can any friend can help me in this.

Thanks Don it works perfect
 
Glad to help.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Try this
Sub putcolstogetherinonecell()
Columns("f").ClearContents
For i = 2 To Cells(Rows.Count, "a").End(xlUp).Row
ms = ""
lc = Cells(i, Columns.Count).End(xlToLeft).Column
For j = 2 To lc
ms = ms & "-" & Cells(i, j)
Next j
Cells(i, lc + 1) = Right(ms, Len(ms) - 1)
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

Hi all, I have data in my sheet (see below)
A B C D E………col
xx 01 02 06 07
zz 55 90 22 78
tt 15 02 05 09
ss 66 58 02 04
aa 18 20 10 03

I want macro which should combine each row cells value from coloum B
to E and put result in coloum F something like this (see below)
A B C D E F……col
xx 01 02 06 07 01-02-06-07
zz 55 90 22 78 55-90-22-78
tt 15 02 05 09 15-02-05-09
ss 66 58 02 04 66-58-02-04
aa 18 20 10 03 18-20-10-03

macro should put result in column F down to last value cell in column
A and macro should make sure that those values in cells which have
zero on the start like 01 or 02 when they will get combine by macro
they should stay in same format like "01-02" instead of "1-2". I hope
I was able to explain my question. Can any friend can help me in this.

Thanks Don it works perfect
 
Back
Top