Use number of characters in a cell as Excel formula criteria

0
votes

I have three columns in a spreadsheet, and my goal is to combine the individual references (column B) where the customer is the same (column A) - but only up to a maximum of 20 characters.

In column E, I use the following formula to combine the references if the value in column A is the same as the cell above: =IF(A2<>A1,"Reference " & B2,E1 & " " & B2)

In column G, I use the following formula to only return the final concatenated value: =IF(A2<>A3,CONCATENATE("",E2,""),"")

However, I'm struggling to find a way to stop combining the references if the cell length contains more than 20 characters, and starting a new concatenation on the next line - like I've put in column I?

Thanks!

spreadsheet

1
excelexcel-formula
Given the first three columns, you could do this in Power Query. But what happens if references are of such a length that you require four or more rows to meet your 20 character limit? – Ron Rosenfeld

1 Answers

0
votes

It seems you're not looking for a parsimonious spreadsheet. Thus, this is what I can think of:

Next to column G, you may add this formula, beginning on row 2:

=IF(LEN(G3)>20,IF(LEN(E2)>20,"",E2),IF(G2="","","Reference "&B2))

Column H (or I, as you wish) will have a 3-step IF statement:

  1. If the length of the FOLLOWING string on column G is greater than 20, then copy the value on E2;
  2. But, if the value on E2 is longer than 20 characters, then return an empty cell;
  3. Finally, if G3 has less than 20 characters AND provided G2 is NOT empty, then return "Reference" + whatever value is on B2. If G2 is empty, then return an empty cell ("").

Thanks to the person who brought the image into the answer. I had to refresh it and that made it unavailable again, shamefully.

This is what it looks like: