Question

Concatenate if then

Forms Version 10

Updated February 6, 2019

asked on February 4, 2019

I have a table with 4 fields and a concatenate if then problem:

Field 1) which we will call A (a drop down pick choice)
Field 2) which we will call B (a number field) (which is hidden if the choice on A is picked as "Other"
Field 3) which we will call "Other" (a single line field that is hidden unless the choice on A is picked as "Other")
Field 4) Field #4 will always be hidden once testing is done and is used only to collect the final input to use elsewhere in the business process).

What I need is to have Field #4:

combine the inputs from A&B for use elsewhere, OR
if the input in A turns out to be picked as "Other", then I want Field #4 to show ONLY what was typed into the Other field.

I can't seem to get the concatenate formula to work. Can you help me figure it out?

0 0

Answer

SELECTED ANSWER

replied on February 4, 2019 • Show version history

If it's in a table it would look like

=IF(INDEX(Field1A,ROW())="Other",INDEX(Field3Other,ROW()),CONCATENATE(INDEX(Field1A,ROW()),INDEX(Field2B,ROW())))

To add your dash with spaces between Field 1 and 2, it would look like

=IF(INDEX(Field1A,ROW())="Other",INDEX(Field3Other,ROW()),CONCATENATE(INDEX(Field1A,ROW())," - ",INDEX(Field2B,ROW())))

This of course is in the Field4 field

2 0

Replies

replied on February 4, 2019

Try:

=IF(Field1="Other", Field 4, CONCATENATE(Field1&Field2))

This works for me but I can't get a space in between the fields in the Concatenate. I tried Field1&" "&Field2 but that didn't work. Field1&"-"&Field2 worked and put a dash between but it does not like the space.

IfElseFormula.png (17.88 KB)

| Download

0 0

replied on February 4, 2019

Thanks, Jennifer. It's not working for me. Is it because the fields are in a table?

0 0

replied on February 4, 2019

Try putting (1) after each field name. This should pull the first row.

I don't know if your table will have multiple rows but someone with more knowledge than me would need to tell you what to put to work for each row.

0 0

replied on February 4, 2019

Nope, that didn't work either.

0 0

replied on February 4, 2019

Hi Connie, based on your requirements above, it would look like this

=IF(Field1A="Other",Field3Other,CONCATENATE(Field1A,Field2B))

Steve

0 0

View 4 previous replies

replied on February 4, 2019

That's pretty much what I have, other than CONCATENATE(Field2B,Field1A)) because of how I want it to show up.

0 0

replied on February 4, 2019

Tables do seem to be an issue. I can't get it to work in a table either.

0 0

SELECTED ANSWER

replied on February 4, 2019 • Show version history

If it's in a table it would look like

=IF(INDEX(Field1A,ROW())="Other",INDEX(Field3Other,ROW()),CONCATENATE(INDEX(Field1A,ROW()),INDEX(Field2B,ROW())))

To add your dash with spaces between Field 1 and 2, it would look like

=IF(INDEX(Field1A,ROW())="Other",INDEX(Field3Other,ROW()),CONCATENATE(INDEX(Field1A,ROW())," - ",INDEX(Field2B,ROW())))

This of course is in the Field4 field

2 0

replied on February 4, 2019

Oh! I think that worked, Steve, Thanks! First tests are working. I will play with it some more, I I think that is the answer. Thanks to both of you for helping me with this!

0 0

replied on February 4, 2019

Okay, so the concept works. But, instead of the dash between the two fields, how can I get a word before the two field inputs?

I want the result to be Box 123, Alliance, T0B 0A0

(I need to do it this way because users are being lazy and not entering all the parts I need.)

0 0

replied on February 4, 2019 • Show version history

Hi Connie

I just added Box in the Concatenate field. I noticed you were also using a comma and not a dash so I changed that to a comma space.

=IF(INDEX(Field1A,ROW())="Other",INDEX(Field3Other,ROW()),CONCATENATE("Box ",INDEX(Field1A,ROW()),", ",INDEX(Field2B,ROW())))

In the CONCATENATE formula, you can join as many variables or text as you want, you just need to separate them with comma's

ie:

CONCATENATE("A","B","C","D","E") would equal ABCDE

or CONCATENATE(FieldA,FieldB,FieldC,FieldD,FieldE) or a combination of both together

Steve

1 0

replied on February 6, 2019

Thank you so much, Steve! It is working perfectly now!

0 0

You are not allowed to follow up in this post.

Question

Question

Concatenate if then

Answer

Replies

Sign in to reply to this post.