Hello,
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.
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:
