Combinding sevral rows into one row with "-"in betwin if not blank

[oras]

Hei.

I need to get cell A2, C2 and E2 combined into F2 with "-".
But if lets say C2 is empty I dont want the "-"

****************************
exempel 1:
A2:F08
C2:31
E2:XXL

the result in F2should be:
F08-31-XXL
*****************************
exempel 2:
A2:F08
C2:
E2:XXL

the result in F2should be:
F08-XXL
*****************************
An exampel on how it should not be:

A2:F08
C2:
E2:XXL

the result in F2should be:
F08--XXL
*****************************

[oras]

I have tryed =A2&"-"&C2&"-"&E2 but then i get the result: F08--XXL

[zbor]

Try:

=SUBSTITUTE(A2&"-"&C2&"-"&E2,"--","-")

[oras]

Almost there:-)
Thanx zbor for the replay.
I had to change it to =SUBSTITUTE(A2&"-"&C2&"-"&E2;"--";"-") not to get an error.
Now all is ok:-) except if there is only data in A2 and C2 and E2 is blank I get F08-

[zbor]

How about this:

=LEFT(SUBSTITUTE(SUBSTITUTE(A2&C2&E2;A2;A2&"-");C2;C2&"-");2*LEN(A2)+2*LEN(C2)+2*LEN(E2)-1)

If you don't have same fords in A2, C2 and E2 cells. (or part of the words as TE, TES, TEST)

[oras]

Mayby this will help explaine what I am trying to do.
Capture.PNG
The product name in F2 should be F02001-87 not F02001-87-
and the product name in F3 Should be F08097 not F08097-

[zbor]

Try now, I've edited...

Also, instead of picture it's better to upload xls workbook (this is a excel forum not a piant forum )

[micope21]

Hi

Only way to work is do a the simple job and simple code
.
Here put on your workbook.

G2 =IF($C2="","","-")
H2 =IF($E2="","","-")
You can hide them.

Then F2 =$A2&G2&C2&H2&E2

Unless zbor come up with better code.

Cheers

[zbor]

You can use that but in one cell:

=A2&IF(A2="";"";"-")&C2&IF(C2="";"";"-")&E2&IF(E2="";"";"-")

[DGagnon]

you could try one of these options

=A1&IF(C1="","","-")&C1&IF(E1="","","-")&E1

=LEFT(SUBSTITUTE(A1&"-"&C1&"-"&E1,"--","-"),LEN(A1)+LEN(C1)+LEN(E1)+COUNTA(A1,C1,E1)-1)

[oras]

Thanx
=LEFT(SUBSTITUTE(A2&"-"&C2&"-"&E2;"--";"-");LEN(A2)+LEN(C2)+LEN(E2)+COUNTA(A2;C2;E2)-1)

did the trick :-)

Best forum ever:-)