Combine 2 or 3 cells into one?

I have a webform that collects data for a request, Department, Type, Etc. These are separate columns. I would like to somehow filter the some of the information received into one column, which would then be a code for that particular request. The code is then used in other locations as an identifier.

Is there any way to do this? I looked at formulas but didn't really see anything that applied.

Thank you

Find more posts tagged with

Forms

Formulas

Conditional Formatting

Cell linking

Comments

Kennedy Stomps

Hi Angie! Though I don't know exactly what the sheet you're working with looks like, I can think of a way that you might be able to accomplish this using an IF formula and concatenation.

For example, say every time a request is submitted for the Printing department, I want the code to begin with P and every time a request is submitted for our Design department, I want it to begin with D. I could use an IF formula to start building the code based on which department was selected in the "Department column":

=IF(Department1 = "Printing", "P", IF(Department1 = "Design", "D"))

Then, I can add additional IF statements to continue building the code based on the value in the Type column. In this case, imagine we code new job requests ("New Job") with "001" and ongoing projects ("Recurring") with 002:

=IF(Department1 = "Printing", "P", IF(Department1 = "Design", "D")) + IF(Type1 = "New Job", "001", IF(Type1 = "Recurring", "002"))

Alternatively, if you just want to collect the main details from the request into one column, you can simply concatenate the values in another column:

=Department1 + ", " + Type1

This will return the value in the Department cell referenced, then a comma and a space, and finally the value from the Type cell referenced.

Let me know how this works for you.

Angie Hatfield

Hi Kennedy,

Using IF statements look ideal for my needs. I have played around with it and keep getting #Unparseable. I've tried to figure out what I've got wrong but am getting nowhere.

Here's my formula:

=IF(Department = "1 General", "1", IF(Department = "2 Executive", "2")) + =IF(Document Type = "Agent", "AG", IF(Document Type = "Claim", "CL"))

Any suggestions?

thank you for your assistance!

J. Craig Williams

Angie,

You have too many "=" and need both a column and row reference

Document as column name

23 as row

Document23

For columns with spaces, use "[]"

[Document Type]23 not Document Type23

=IF(Department23 = "1 General", "1", IF(Department23 = "2 Executive", "2")) + IF([Document Type]23 = "Agent", "AG", IF([Document Type]23 = "Claim", "CL"))

You should also likely account for blanks or unrecognized entries, perhaps like this:

=IF(Department23 = "1 General", "1", IF(Department23 = "2 Executive", "2", "D")) + IF([Document Type]23 = "Agent", "AG", IF([Document Type]23 = "Claim", "CL", "DT"))

where a missing/unrecognized Department will show up as D and a missing/unrecognized Document Type will show up as DT

I hope this helps.

Craig

Angie Hatfield

Craig,

That absolutely does work, thank you very much!

Obviously I am new to IF statements, but I think I am going to become very good at them. I see a lot of potential to make vast improvements to my sheets.

Thank you again, I appreciate your help!

Angie Hatfield

What is the proper way to add a third and maybe a fourth column to this equation?

Now i want to combine Department, Document Type, I/O (Incoming or Outgoing, and module.

I messed around trying to get it to work, but I keep getting #unparseable.

I'm not sure how the third (and fourth) should be displayed. I've tried removing one or two of the end brackets, I've tried renaming my column from I/O to I O, amongst other things.

=IF(Department21 = "1 General", "1", IF(Department21 = "9 Operations", "9")) + IF([Document Type]21 = "Contract", "CO", IF([Document Type]21 = "Claim", "CL")) + IF(I/O21 = "Incoming", "I", IF(I/O21 = "Outgoing", "O", IF(I/O21 = "Both", "B")))

I'm getting frustrated with myself for not being able to figure this out or locate the answer!

thank you very much!

Angie Hatfield

I figured it out. I had to change I/O to IO and I have the correct amount of brackets.

Angie Hatfield

I just want to say WOW, this is one of the most powerful advantages over using Excel. I can now have a code generated based on information submitted on a web form and requires no work on my part. The codes are perfectly standardized to our needs and follow coding used in other parts of our Company.