Concatenate and ignore blanks

Thanks guys,

How would I then get each bit to go on a new line using the CHAR(10) thing?

also NBVC,

your formula worked tho it cut off the "/09)" off the last date for some reason

thanks andy i misread yours

thanks nbvc - think this way is simpler! :D

cheers all

hey guys,

sorry to be a pain but VBA is really not my thing but i gotta get this done today!

I've got this other sheet where I need to now concatenate 3 columns in the same way though the format would be
Name1 (Position1) - 1 days, Name2 (Position 2) - 2 days etc.

The columns I need to concatenate are D, E and G

Any ideas?

have attached the other file

thanks and sorry for being dumb!

p.s. column E is a lookup

either nbvc
i checked ur method and it still kept cutting even though i had wrap text checked...
i just hope it works :D

actually would be good to use urs nbvc
at least i'd learn then!

that looks good but when i entered that formula into a cell and confirm with CTRL+SHIFT+ENTER it just says #Name.

Am I supposed to have some code put in a module?

so i put in the code that you posted before into the module and then put in the formula u supplied into the cell and the ctrl shift and enter?

cool it worked!

thanks! will have a play round to get the format i want

hey just checked again and for some reason it's still cutting the end of the strings?

for example in this one it just says

Nick Senior

as opposed
to

Nick Senior Manager 3

figured it out...
the last number at the end should have been a *1 instead of *2

thanks again

sorry one last thing...
it is definitely still cutting things off.

is this just a limit of characters allowed in the cell?

hey it's that number on the end that is messing things up...

if i do "/3" i get more characters.

is there no way for it just to take everything?

cool that's much better...
that allows 13 names at least...
if they want more than i'll just tell them it's not possible