Using IF within a CONCATENATE formula

[Dgmknzgrl]

I'm trying to represent dimensions using concatenate and IF statements but it's not working.

This is what I'm going for

If HEIGHT and WIDTH then H x W in.

If HEIGHT and WIDTH and LENGTH then H x W x L in.

If DIAMETER then D dia.

I managed to get the first two fields working, but I'm not sure how to get the dia field to show up.

IF({Length (in)}=0,{Height (in)} & " x " & {Width (in)} & " in.", {Height (in)} & " x " & {Width (in)} & " x " & {Length (in)} & " in.")

I also tried the following based on reading some other threads and the formula works (without the & so I didn't get too confused), but doesn't actually produce anything (the Dimensions field stays blank):

IF(AND({Length (in)}=0,{Height (in)}&" x "&{Length (in)},IF(AND({Length (in)}>1,{Height (in)}&{Width (in)}&{Length (in)},IF(AND({Diameter (in)}>1,{Diameter (in)}))))))

Any help would be appreciated!

[Brian_Swanson]

@Dgmknzgrl I would approach this in this manner:

IF(AND({Height (in)}, {Width (in)}, {Length (in)}=BLANK()),

CONCATENATE({Height (in)}," x ", {Width (in)}),

IF(AND({Height (in)}, {Width (in)},{Length (in)}),

CONCATENATE({Height (in)}," x ", {Width (in)}," x ",{Length (in)}),

IF({Diameter}, {Diameter}, BLANK())

))

This first if statement looks for a value in height and width but a blank value in length and returns a concatenation reflecting that

The second if statement looks for a value in height, width, and length and returns the appropriate concatenation 

The last if just looks to see if there is a value in the diamter field and returns that if there is one.  If there isn't any of the these conditions it just leaves the field blank. 

Hope that helps.  

[Ben_Young1 · Accepted solution]

Hey @Dgmknzgrl! 

Give this a shot!

TRIM(
    IF(
        {Height (in)},
        TRIM({Height (in)}) & IF(
            OR({Width (in)}, {Length (in)}),
            " x ",
            IF(
                NOT({Length (in)}),
                " in.\n"
            )
        )
    )
    &
    IF(
        {Width (in)},
        TRIM({Width (in)}) & IF(
            {Length (in)},
            " x ",
            " in.\n"
        )
    )
    &
    IF(
        {Length (in)},
        TRIM({Length (in)}) & " in.\n"
    )
    &
    IF(
        {Diameter (in)},
        TRIM({Diameter (in)}) & " dia."
    )
)

 Here's what it looks like when used:

Be sure to check that your field names are properly formatted if the formula throws you an error.

[Dgmknzgrl · Accepted solution]

@Brian_Swanson thanks much!