Merging Table Cells with Text under Certain Conditions

**verdecove** · 08-31-2009, 11:09 AM

Hi Everyone,
I hope I'm posting this in the right section of this forum.
Have been using Excel Spreadsheets with simple formulas in a while at work but now I was going to use Excel in a way I do not know how to get it
working for me. I have no clue about VB Scripts or Makros in general or creating formulas with LookUp or Check if a cell value if True or False (Cell is
filled or empty).

Here is what I need and I hope someone in this forum can help me out.

From a supplier I will get a spreadsheet including the the following Information which I need to convert into an url including trailing slash (/) for an online store.

Column A = Value of Main Cartegory (e.g. Holidays)
Column B = Value of Subcategory_1 (e.g. Christmas Decor)
Column C = Value of Subcategory_2 (e.g. Candle Holders)

Those raw data are without any trailing slashes.
Now I want to create a column e.g. Column D (product_url) which should be a summary of column A thru C but if there is only a Value for Main Category
there should be no trailing slash,
if there is a Value for Main Category and a Value for Subcategory_1: no trailing slash after Subcategory_1 but trailing slash between Main Category and Subcategory_1.

The result should look like this:
Holidays (Main category)
Holidays/Christmas Decor (Main category & Subcategory_1)
Holidays/Christmas Decor/Candleholders (Main category & Subcategory_1 & Subcategory_2)

Is it possible to create something like that in Excel at all since all values will only be text and the trailing slash is actually functional for Excel calculations but in this case it would have to be treated as "text".

Any help will be greatly appreciated since I am talking about more than 3500 products (and increasing) I will have to create the right url's for.
Thank You
verdecove

**Palmetto** · 08-31-2009, 03:15 PM

Although you have posted in the Programming section of the Forum, you could use a formula for this - they are generally more efficient than using VBA.

Assuming row1 is a header row.
in cell D2:

=IF(AND(LEN(A2)>=1,LEN(B2)>=1,LEN(C2)>=1),A2&"/"&B2&"/"&C2,IF(AND(LEN(C2)<1,LEN(B2)<1),A2,A2&"/"&B2))

**verdecove** · 08-31-2009, 06:09 PM

Hi Palmetto,
have tried the formula but it is not working.
Excel marks...
=IF(AND(LEN(A2)>=1,LEN(B2)>=1,LEN(C2)>=1),A2&"/"&B2&"/"&C2,IF(AND(LEN(C2)<1,LEN(B2)<1),A2,A2&"/"&B2))
and I receive an Excel Standard Error Message.

I still would like to thank you for your effort helping me and maybe you self or someone else will figure out why the error message occurs.

verdecove

**Palmetto** · 08-31-2009, 06:40 PM

have tried the formula but it is not working.

Works for me. See attached.
I did forget to factor in a test for A2 being blank and have amended the formula.

=IF(A2="","",IF(AND(LEN(A2)>=1,LEN(B2)>=1,LEN(C2)>=1),A2&"/"&B2&"/"&C2,IF(AND(LEN(C2)<1,LEN(B2)<1),A2,A2&"/"&B2)))

**Leith Ross** · 08-31-2009, 06:45 PM

Hello verdecove,

Using a formula solution will recreate these URLs each time the sheet is activated, unless you set calculation to manual. With a macro you can click a button when needed to create them. If you can post a sample workbook with the URLs, I can create a custom macro solution for you.

**verdecove** · 08-31-2009, 07:49 PM

Originally Posted by Palmetto

Works for me. See attached.
I did forget to factor in a test for A2 being blank and have amended the formula.

=IF(A2="","",IF(AND(LEN(A2)>=1,LEN(B2)>=1,LEN(C2)>=1),A2&"/"&B2&"/"&C2,IF(AND(LEN(C2)<1,LEN(B2)<1),A2,A2&"/"&B2)))

Thank You sooooo much!!! It does work on my end too. You have no clue how much time and stress you have saved me.

verdecove

**verdecove** · 08-31-2009, 07:52 PM

Originally Posted by Leith Ross

Hello verdecove,

Using a formula solution will recreate these URLs each time the sheet is activated, unless you set calculation to manual. With a macro you can click a button when needed to create them. If you can post a sample workbook with the URLs, I can create a custom macro solution for you.

Thank You Leith, I appreciate you have been trying to help me too.
The formula Palmetto has created for me does work just fine.
I don't know anything about Makros and how to install them and so I will just work with the formula instead.
But like I said, I really appreciate your help.
Thank You s much
verdecove

**Palmetto** · 08-31-2009, 07:56 PM

Thank You sooooo much!!! It does work on my end too. You have no clue how much time and stress you have saved me.

Glad to have helped.

I once lived in Houston and have some kinfolk near Dallas (Mesquite, actually), and do I ever miss some good mesquite-smoked Texas Beef BBQ.

**verdecove** · 08-31-2009, 08:08 PM

Originally Posted by Palmetto

Glad to have helped.

I once lived in Houston and have some kinfolk near Dallas (Mesquite, actually), and do I ever miss some good mesquite-smoked Texas Beef BBQ.

The world is so small! I'm living in Spring, north of Houston. Although originally I'm from Germany. Moved over here about 4 years ago. My wife is originally from here. And yes, the Texas BBQ is really good to die for.
Once again thanks for your help. If I ever will run into another problem with Excel I will contact you if you don't mind.
take care
verdecove