How best to copy partial information from one column into another?

I'd be interested to know if there's a more succinct way to do this as well, since your approach is how I would build this out.

It sounds like you may be hitting cell character limits. What about splitting the formula into the First 40 and then adding another column to return the Second 40, and in a third column add together the two outputs?

Cheers,

Genevieve

Hi @Genevieve P. - Thanks. That seems like a workable option, though hopefully others may have some thoughts on a more succinct option for us. Best - David

The great thing is that after you've created the two longer formulas, combining them into the new column will be very easy!

Ex:

=[Column 1]@row + " " + [Column 2]@row

In regards to splitting up the company names, what about having the columns be Multi-Select and then use CHAR(10) after each name? This would break each name out into a separate value.

I would actually say since your final column will be adding the two cells together, if none of the company names are shows then we can put that statement in your final formula instead of into the two halves.

Ex:

=IF(AND([Formula 1]@row = "", [Formula 2]@row = ""), "None", [Formula 1]@row + CHAR(10) + [Formula 2]@row

Screen Shot 2022-01-26 at 7.42.05 PM.png

So then your two halves would go right into your IF(CONTAINS statements:

=IF(CONTAINS("Company One", [COLUMN A]@row), "Company One" + CHAR(10)) + IF(CONTAINS("Company Two", [COLUMN A]@row), "Company Two" + CHAR(10)) + IF(CONTAINS("Company Three", [COLUMN A]@row), "Company Three" + CHAR(10)) + IF(CONTAINS("Company Four", [COLUMN A]@row), "Company Four" + CHAR(10)) + IF(CONTAINS("Company Five", [COLUMN A]@row), "Company Five" + CHAR(10))

... etc

Screen Shot 2022-01-26 at 7.39.48 PM.png

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

My apologies for the delay!

The 0's are really interesting, it may have to do with the fact that we haven't told the IF statements what to do if it doesn't find that value.

Try adding , "" to the end of each IF so that it returns Blank. Also, you don't need the extra ( ) around your IF statements.

Try:

=IF(ISBLANK(ANALYSTS@row), "None", (IF(CONTAINS("company a", ANALYSTS@row), "company a" + CHAR(10), "") + IF(CONTAINS("company b", ANALYSTS@row), "company b" + CHAR(10), "") + IF(CONTAINS("company c", ANALYSTS@row), "company c" + CHAR(10), "") + IF(CONTAINS("company d", ANALYSTS@row), "company d" + CHAR(10), "")))

All comments

I'd be interested to know if there's a more succinct way to do this as well, since your approach is how I would build this out.

Cheers,

Genevieve

Hi @Genevieve P. - Thanks. That seems like a workable option, though hopefully others may have some thoughts on a more succinct option for us. Best - David

Hi @Genevieve P. - Haven't seen any other options offered so I'm going to handle the formula in the manner you suggest... splitting the formula into 2 columns. Do you know how you would then combine the outputs into a singular column?

Also, in the cells where the result is multiple company names, how would I either include a semicolon or some other divider between each company name? (I think I can just include that after each company name in the formula, but then I also want to eliminate that divider when only one company name populates into the cell. Any ideas on that one?

All the best,

David

The great thing is that after you've created the two longer formulas, combining them into the new column will be very easy!

Ex:

=[Column 1]@row + " " + [Column 2]@row

In regards to splitting up the company names, what about having the columns be Multi-Select and then use CHAR(10) after each name? This would break each name out into a separate value.

Ex:

=IF(AND([Formula 1]@row = "", [Formula 2]@row = ""), "None", [Formula 1]@row + CHAR(10) + [Formula 2]@row

So then your two halves would go right into your IF(CONTAINS statements:

... etc

Let me know if that makes sense and will work for you!

Cheers,

Genevieve

Hi there @Genevieve P. Thumbs up! Thanks for coming back on this. CHAR (10) is a great solution to the divider... much cleaner than how I was approaching! I'll work through your formulas tomorrow, but can see how this will solve my conundrum. Thank you!

I'm glad I could help! 🙂

Hi @Genevieve P. Just wanted to circle back with an interesting hiccup. The formulas are doing what they are suppose to but there is some weirdness in the results that I can't figure out. Maybe you've come across this: When there isn't a company name listed in both Formula columns the numeral zero appears in the column without a company listed. And then that zero is populated into the combined column.

There are also instances where a zero will appear in front of the company name "0company e" for instance instead of "company e" in one of the Formula columns (and of course that gets pulled over to the combined column as well). This seems to have something to do with certain combinations of company names, or where they sit within the formula. It is not random, but I can't establish an exact pattern.

FIRM1 Column...

=IF((ISBLANK(ANALYSTS@row)), "None", (IF(CONTAINS("company a", ANALYSTS@row), "company a" + CHAR(10)) + IF(CONTAINS("company b", ANALYSTS@row), "company b" + CHAR(10)) + IF(CONTAINS("company c", ANALYSTS@row), "company c" + CHAR(10)) + IF(CONTAINS("company d", ANALYSTS@row), "company d" + CHAR(10))))

FIRM2 Column...

=IF((ISBLANK(ANALYSTS@row)), "None", (IF(CONTAINS("company e", ANALYSTS@row), "company e" + CHAR(10)) + IF(CONTAINS("company f", ANALYSTS@row), "company f" + CHAR(10)) + IF(CONTAINS("company g", ANALYSTS@row), "company g" + CHAR(10)) + IF(CONTAINS("company h", ANALYSTS@row), "company h" + CHAR(10))))

Weird?

My apologies for the delay!

The 0's are really interesting, it may have to do with the fact that we haven't told the IF statements what to do if it doesn't find that value.

Try adding , "" to the end of each IF so that it returns Blank. Also, you don't need the extra ( ) around your IF statements.

Try: