Utilizing a Join formula while KEEPING a "%" character.

hannah_johnson

I have a column with multiple values inside it with a line break delimiter. It is a result of a Join formula which looks at more than 100 columns to join them together (there's typically no more than 3 values joined). The values being joined have a "%" after them in the source cells, but the join formula is not bringing the "%" with it. How do I keep the values coming in on separate lines within the cell AND keep a % after each?

Right now, the join formula is bringing the numbers needed together with line breaks within each cell, which is great. But the numbers in the source cells have %s after them, and they disappear when brought into this formula. Help!!! I need them to state #% [line break] #%, etc.

=JOIN([Column 1@row:[Column 100]@row, CHAR(10))

SoS | Dan Palenchar

Hello @hannah_johnson,

You can do this 2 ways, see below.

Option 1

Use this formula. I set it up with columns named V1, V2, V3.

IF(ISNUMBER([V1]@row), [V1]@row * 100 + "%" + CHAR(10), "") + IF(ISNUMBER([V2]@row), [V2]@row * 100 + "%" + CHAR(10), "") + IF(ISNUMBER([V3]@row), [V3]@row * 100 + "%")

The output looks like this:

Option 2

It looks like you are joining 100 columns, so it's probably not practical to expand the formula for all of those values. What you can do in this case is make 100 new columns where each column where have a formula that converts the % values into text values that display as a %. You can then JOIN(COLLECT()) these as you already have. It looks like this:

To speed this process up you can copy a row to another Sheet than copy it back. All of your columns will appear in your Source sheet with a (1) after them. Then, in what will be Column 1 (1) you can input the formula:

=IF(ISNUMBER([Column 1]@row), [Column 1]@row * 100 + "%")

Once that is in you can just drag it to the other 99 columns.

Finally, update your JOIN() formula to be

=JOIN(COLLECT([Column 1 (1)]@row:[Column 100 (1)]@row, [Column 1 (1)]@row:[Column 100 (1)]@row, ISTEXT(@cell)), CHAR(10))

Note I am using JOIN() with COLLECT() to only take in values that exist. With just JOIN() there will be blank rows representing any missing values. If you want these blank rows than just use the JOIN() portion.

Access a published Sheet here or interact with it below:

https://app.smartsheet.com/b/publish?EQBCT=2f337d3a298a4aa2b232fc6047db35f4

Hope this helps!

Nick Korna

Hi @hannah_johnson,

You should be able to do it with a single formula on the row (substitute your column names and this is assuming they're all consecutive:

=JOIN(COLLECT([Column 1]@row:[Column Last]@row, [Column 1]@row:[Column Last]@row, NOT(ISBLANK(@cell))), "%" + CHAR(10)) + IF(COUNTM([Column 1]@row:[Column Last]@row) > 0, "%", "")

Sample output:

Hope this helps, but if you've any problems/questions, just let us know!

hannah_johnson

@Nick Korna Ohhhh this gets me SO close. Our numbers are in decimals - any suggestions for a slight adjustment to your formula to multiply by 100? I have tried adding a few places and it isn't liking where I add it.

Nick Korna

@hannah_johnson,

I don't think you can do this in a single cell, but you can with a series of helper columns (which you'd presumably hide!).

Here the "Initial" column is using the formula as above - this is largely to join your values together.

The formulas for the helper columns are then:

1st:

=VALUE(LEFT(Initial@row, FIND("%", Initial@row, 1) - 1)) * 100

2nd:

=IFERROR(VALUE(MID(Initial@row, FIND("%", Initial@row, 1) + 2, (FIND("%", Initial@row, (FIND(CHAR(10), Initial@row))) - FIND(CHAR(10), Initial@row) - 1))), "") * 100

3rd:

=IFERROR(VALUE(MID(Initial@row, FIND("%", Initial@row, (FIND(CHAR(10), Initial@row))) + 2, FIND("%", Initial@row, (FIND(CHAR(10), Initial@row, FIND(CHAR(10), Initial@row) + 1))) - (FIND("%", Initial@row, (FIND(CHAR(10), Initial@row))) + 2))) * 100, "")

5th:

=IFERROR(VALUE(MID(Initial@row, FIND("%", Initial@row, (FIND(CHAR(10), Initial@row, FIND(CHAR(10), Initial@row) + 1))) + 2, FIND("%", Initial@row, FIND("%", Initial@row, (FIND(CHAR(10), Initial@row, FIND(CHAR(10), Initial@row) + 1))) + 1) - FIND("%", Initial@row, (FIND(CHAR(10), Initial@row, FIND(CHAR(10), Initial@row) + 1))) - 2)) * 100, "")

5th:

=IFERROR(VALUE(MID(Initial@row, FIND("%", Initial@row, FIND("%", Initial@row, (FIND(CHAR(10), Initial@row, FIND(CHAR(10), Initial@row) + 1))) + 1) + 2, FIND("%", Initial@row, FIND("%", Initial@row, FIND("%", Initial@row, (FIND(CHAR(10), Initial@row, FIND(CHAR(10), Initial@row) + 1))) + 1) + 1) - FIND("%", Initial@row, FIND("%", Initial@row, (FIND(CHAR(10), Initial@row, FIND(CHAR(10), Initial@row) + 1))) + 1) - 2)) * 100, "")

This gives your number for the percentage, which is then rejoined in the final "Rejoin" column:

=JOIN(COLLECT([1st helper]@row:[5th helper]@row, [1st helper]@row:[5th helper]@row, NOT(ISBLANK(@cell))), "%" + CHAR(10)) + IF(COUNTM([1st helper]@row:[5th helper]@row) > 0, "%", "")

This only works for the first 5 values, but as you said it's usually less than that it should function. If you do have more, then you can have additional columns, but those nested FINDs start getting longer and longer…

In the initial column you can use another symbol in place of the % as delimiter, or in theory if you amended it to put a CHAR(10) at the end you could use this, but it is a lot easier to spot any issues with something visible! 😉

Hope this resolves your problem, but let me know if there are any issues!