formulasgoogle sheetsgoogle-sheets-arrayformularegex
I'm trying to create a formula that will replace any contents of comma delimited cell with single string. The number of items in delimited cell will vary…
Specifically:
524352435,2345235523,2345235235,sfdsfsdsf,423534534 needs to become 1,1,1,1,1
1231231,bbssdds,464574745 needs to become 1,1,1
Any thoughts greatly appreciated. Thanks
You can use REGEXMATCH:
=IF(REGEXMATCH(A1, "sites"), 1, 0)
To explain, REGEXMATCH returns true if and only if the argument is a substring of your string.
=ARRAYFORMULA(IF(LEN(A1:A); IF(EXACT(UPPER(LEFT(A1:A; 1));
LEFT(A1:A; 1)); TRUE; FALSE); ))
Best Answer
You can try the following
(Please adjust ranges to your needs)