How to combine several vertical cells in one cell

**malnahar** · 06-13-2008, 08:07 AM

Dears,

Please help, the data available in the attached are exported data
The data available in (d2:d8) is the description for the amount available in (e2), then the data available in (d9:d10) is the description for the amount in (e9), the data available in (d11:d14) is the description for the amount in (e11) etc.

Now my question : is there any way to combine the data availabl in (d2:d8) in (g2) and then (d9:d10) in (g9) and then (d11:d14) in (g11) etc?

Thanks

**arthurbr** · 06-13-2008, 08:21 AM

Hi and welcome to the board

you can combine text from different cells :

1. Using the CONCATENATE function
2. Using the "&" operator ( f.e. to combine contents of a1 and a2 use =a1&a2)

Cheers

**malnahar** · 06-13-2008, 10:50 AM

Originally Posted by arthurbr

Hi and welcome to the board

you can combine text from different cells :

1. Using the CONCATENATE function
2. Using the "&" operator ( f.e. to combine contents of a1 and a2 use =a1&a2)

Cheers

Thank you, this will be practical when you have few data, but what would be the solution for huge data ; 5000 rows for example, please note that the number of cells containing the description are changing randomly; back to the attached file, please note that there are 7 cells (d2:d8) to descripe e2 and then 2 cells (d9:d10) to descripe e9 etc

Many thanks in advance

**Mark@Work** · 06-13-2008, 11:30 AM

I assume that cells in column e that dont match the start of the list in column d are empty.
May answer is not quite what you asked for, but close...

G1 : ""
G2 : IF(ISBLANK(E2),G1&D2,D2)
G3 : IF(ISBLANK(E3),G2&D3,D3)
replicate this down and the addresses should automatically update.

This will give you the result you want but not in the cell you want!

Mark.

**mikeyfear** · 06-13-2008, 11:51 AM

How about a custom function:

Please Login or Register  to view this content.

Credit to the original code writer.

To combine any cells just type =concat(A1:A10) for example.

**Mark@Work** · 06-13-2008, 11:58 AM

Ok, to get the result in the correct cell:

H2: =IF(ISBLANK(E2),H1&D2,D2)
I2: =LEN(H2)<LEN(H3)
G2: =IF(ISBLANK(E2),"",INDEX(H2:H102,MATCH(FALSE,I2:I102,0)))

select these three cells and replicate down.
Hide columns I and H
(it does not have to be these columns, but the addresses would have to change appropriately)
Note that I have assumed that there are never more than 100 lines describing the amount...

This should work, but there might be a better way.
Mark.

**malnahar** · 06-13-2008, 12:47 PM

Many big Thanks Mark, appreciate your help, this is fantastic

One more small question please: is it possible to have one space between the descriptions picked from colomn d when gathered in cells of coloumn g?

I tryed to include " " in the formulas but could not manage.

Thanks in advance.

Hi Mike,

Thanks for the code, i will be back in touch soon

Best regards

**Mark@Work** · 06-16-2008, 05:58 AM

Off the top of my head (without actually trying it) this should work:
H2: =IF(ISBLANK(E2),H1&" "&D2,D2)
or possibly:
H2: =IF(ISBLANK(E2),H1&D2,D2&" ")
The first option might give you an unwanted blank at the start,
the second will give you an extra blank at the end.

If leading or trailing spaces are an embarrassment, you can use the TRIM() function.

G2: =IF(ISBLANK(E2),"",TRIM(INDEX(H2:H102,MATCH(FALSE,I2:I102,0))))