Concatenate IF cell is not empty

[GMSuppliesltd]

Hi,

Hoping someone can help me, I have been sent a very large file of product data from one of my suppliers, They sent all of the details in separate fields (for example, colour, height, width, depth, material etc)

I have processed all these columns to appear how I want them, but now need to join them together so they can all be added to my main description. I want each one to be on a new line when it's displayed on my website, so I'll be adding a <br /> tag (break tag) after each item. This I can do no problem.

This would all be fine, if all the columns contained data, but a lot of the time the columns do not contain data (eg. I have a column for "knife length" but not all of my products are knives, so not all of them require this information"

for example, I have something like:

SKU | Colour | height | width | depth | material
ABC | blue | 50mm | 10mm | 60mm |
BCD | | 80mm | 75mm | 30mm | wood
CDE | red | | | | plastic

I could use something like :

=CONCATENATE($A2, " <br /> ", CHAR(10),$B2, " <br /> ", CHAR(10),$C2, " <br /> ", CHAR(10),)

This however means writing a piece of code for each cell I want to join, My cells go from range "O2" to "DW2", I don't fancy writing that for every single column!!
Also, it means that If I have some empty fields, it will still add a break tag, this will look very odd on my site.

I really need a way of writing into the function "include if cell contains data" for each cell. If it doesn't contain anything, skip it and move to the next.
(the char(10) in this just gives me a line break in excel so I can see what it'll look like when it's exported)

Any help would be greatly appreciated.

Thanks!

Chris

[martindwilson]

Please Login or Register  to view this content.

but if stuff in cells have spaces in them this wont work

[GMSuppliesltd]

Thanks, but a lot of the stuff is full sentences, so they do have spaces in them

[adyteo]

to solve one of your problem (the second part) is this:

Please Login or Register  to view this content.

now, for you to avoid including in the formula your hundred columns, you would probably need a macro.

[martindwilson]

well unless someone writes a udf for you
what i'd do would be to select my range and find replace space with ^
then use the function
once done id copy /paste special/ values to another sheet
then find ^ replace with space

[GMSuppliesltd]

Martin,

I never thought of that!

Replaced all the spaces with a ^, then used your code and it looks to have done exactly what I wanted it to do!

Thank you!

[arlu1201]

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.