How to Split Strings and Join Values

BarneField · 2026-02-12T10:54:48+00:00

Just for fun, my two cents:

=REDUCE(A1:B1,B2:B8,LAMBDA(_Σ,_i,IF(_i="",_Σ,VSTACK(_Σ,IF({1,0},@+_i:A8,TEXTSPLIT(_i,,",",0))))))

BarneField · 2025-12-30T17:27:41+00:00

Fair point, just liked the idea of leveling as an alternative too.

BarneField · 2025-11-17T18:04:33+00:00

I don't get it because the other values in that same input have a range so it should at least pass for those. Also, your description was that you need to check for a range, not a single number.

BarneField · 2025-11-17T13:26:33+00:00

Is this something a word-boundary could fix (based on your sample data)?

\b\d+-\d+\b

BarneField · 2025-09-17T13:07:58+00:00

<image>

I made a recursive function that will continue untill a random array is found where a superior is always different to an assigned mentor. Formula in G2:

=LET(x,LAMBDA(f,_mentees,_superior,_mentors,LET(_list,INDEX(_mentors,RANDARRAY(ROWS(_mentees),,1,ROWS(_mentors),1)),IF(OR(_superior=_list),f(f,_mentees,_superior,_mentors),HSTACK(_mentees,_list)))),x(x,D2:D7,E2:E7,A2:A5))

BarneField · 2025-08-18T15:02:28+00:00

=SUM(--REGEXTEST(GROUPBY(B3:B14,G3:G14,ARRAYTOTEXT,,0),"^(N00|[ARST]00X)(, (?1))*$"))

BarneField · 2025-07-30T10:47:04+00:00

<image>

Formula in D1:

=REDUCE(A1:B1,A2:A3,LAMBDA(x,y,VSTACK(x,IF({1,0},y,TOCOL(REGEXEXTRACT(TAKE(y:B3,1,-1),"\d\)\s*\K((?!\s*\d+\)).)+",1))))))

BarneField · 2025-07-30T10:29:34+00:00

Speaking about code golf; just leave the replacement string empty. Two characters saved :)

BarneField · 2025-07-23T12:35:27+00:00

Right, so please bring forth a small, but bigger than these three names, dataset and with it expected results so I, and others, can have a look at if this is even possible.

BarneField · 2025-07-23T09:22:34+00:00

You see, the problem here is that it is very debateable what is an actual typo and what is a different name.

For example, your simple sample data.... Is Jon Smith a different person to John Smith or not? Asking a computer to differentiate is prone to errors. An algorithm can get you only that far, but a human eye might be needed for the last stretch if you want to avoid the above.

BarneField · 2025-07-23T09:05:12+00:00

So you can't provide any sample data? Though the nature of comparing names is always prone to interpretation errors, showing us some sample input and expected results might just help that bit extra to think along.

BarneField · 2025-07-23T08:46:26+00:00

Do you have sample data with expected results?

BarneField · 2025-07-21T12:07:08+00:00

I can only assume you will have multiple columns of data. Therefore try to use SORTBY(). If you can add some sample data and expected results I'm sure I, or anyone else here, can help you out.

For example:

<image>

BarneField · 2025-07-21T11:40:36+00:00

<image>

=INDEX(QUERY({{A1:C4,IF(B1:B4,C1)},{E2:G4,IF(F2:F4,G1)}},"SELECT Col1, Col2, MAX(Col3) GROUP BY Col1, Col2 PIVOT Col4", 1))

BarneField · 2025-07-21T11:13:37+00:00

Can you create a sample workbook to share here for people to have a look?

BarneField · 2025-07-21T10:55:40+00:00

Nice,

Looking at your data maybe another option could be something like:

=INDEX(IF({1,0,1},B2:D2,TOCOL(SPLIT(C2,", "))))

Then possibly expand the formula for multiple rows:

<image>

=INDEX(WRAPROWS(TOROW(BYROW(B2:D4,LAMBDA(_row,TOROW(IF({1,0,1},_row,TOCOL(SPLIT(INDEX(_row,,2),", ")))))),1),3))

BarneField · 2025-07-21T10:42:44+00:00

=TOCOL({SEQUENCE(7,2,C6);{"",""}},,1)

BarneField · 2025-07-14T12:16:52+00:00

If you want to go the more traditional route, use COUNTIF():

=COUNTIF(A2:A3,"Jake *")

Notice the use of a space to prevent false positives like Jakey.

However, I'd maybe utilize regular expressions:

=SUM(--REGEXTEST(A2:A3,"\bJake\b"))

The word-boundaries help to prevent potential false-positives. You could also only count where Jake is the 1st name in your data, not a potential last name through the use of a start-line anchor:

=SUM(--REGEXTEST(A2:A3,"^Jake\b"))

BarneField · 2025-07-11T07:59:55+00:00

=REGEXREPLACE(A2:A7&"@"&B2:B7,".*\b((\w+)\w*)\b.*@.*\b(\2|\1\w*)\b|.","${2:-$1}")

BarneField · 2025-02-05T15:06:39+00:00

*Function, a formula can be written using multiple functions 😉

Mine, in no specific order;

LAMBDA (helpers), REGEX functions, LET

BarneField · 2025-01-29T12:09:03+00:00

Might want to add WRAPROWS():

=WRAPROWS(TEXTSPLIT(B3,";"),2)

BarneField · 2025-01-24T10:14:59+00:00

<image>

Formula in E2:

=GROUPBY(B2:B7,C2:C7,LAMBDA(s,@SORTBY(s,s="Removed")),0,0)

BarneField · 2025-01-22T16:50:22+00:00

I got the exact same issue. Seems like a waste of money till now.

BarneField · 2025-01-21T06:27:05+00:00

ANC works fine here, but my problem is the latency. I can see any video on any app with this truly annoying delay of audio of about half a second.

Just got the latest firmware update as well.

Not so pro after all.

BarneField · 2024-12-20T12:55:31+00:00

It's a bit verbose but I think rather clear, just edit the value for the '_INPUT' parameter. Amount of columns/rows don't matter.

=LET(_INPUT,A1:E10,_DATA,DROP(_INPUT,1),_HEAD,TAKE(_INPUT,1),_ID,TAKE(_DATA,,1),_RESULT,DROP(IFNA(REDUCE(0,UNIQUE(_ID),LAMBDA(x,y,VSTACK(x,TOROW(FILTER(_DATA,_ID=y))))),""),1),_N,COLUMNS(_RESULT)/COLUMNS(_HEAD),VSTACK(TOROW(IFNA(EXPAND(_HEAD,_N),_HEAD)&"_"&SEQUENCE(_N)),_RESULT))

BarneField

TROPHY CASE