combining texts (many many cells) in a smart way


New Member
Nov 11, 2018
I’m having a difficult time creating a function that allows me to combine texts.

Suppose I have a data set as shown in “data set” and would like to know if a sentence includes that specific text.
I successfully made a set of functions including find/lookup/iferror which provides values shown in data set if and only if that text is found in a sentence and provides nothing if it doesn’t.

Then here’s the real issue,
I want to make an outcome as shown in “desired output” which divides all the values obtained with “|”. If there is not value, we want to omit this step so that I can avoid myself from seeing something like “280|270|” or “280||”

Two questions I guess
1) Is there a way to make this happen (A function that skips this process if the cell is “”)? I can make the function outcome anyway I want so any modification is possible (e.g. from “” to 0 or -1 in case the text is not found in a sentence)
2) This is a bigger issue. While my example seems quite simple, the real data set contains ~4000 inputs(such as math and science as shown in the example). I cannot possibly type =C11&”|”&D11&”|”…. For 4000 times, so was wondering if there is a way to simplify this process. (such as from “=A1+A2+A3…+A100” to “sum(A1:A100)” I thought of concatenate as well, but it does not solve this issue.

Thank you! I always receive great feedback and guidance here and always appreciate.
Last edited:


Active Member
Dec 3, 2009
Re: combining texts (many many cells) in a smart way. Guidance needed!

If you have excel 2016 the textjoin function might do this -- I'm not certain because I don't have it.

I use the Nutilities addin for this myself. If you have excel 2007 and up, you can use it too -- it's free.

It can be downloaded here:

Once you have it installed, the below link has a video on now it works:

Forum statistics

Latest member

Some videos you may like

This Week's Hot Topics