1

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

3
  • =REGEXREPLACE(REGEXREPLACE(A1,",\w+",",1"),"\w+,","1,") – Oleg_S Nov 19 '20 at 13:35
  • This is amazing - the dark arts of REGEXREPLACE. 8•) How should I adapt this to work if there is only one item (ie no comma delimiter) - Thanks – Mr_P Nov 19 '20 at 14:17
  • figured it out - thanks @Oleg_S ... =REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A1,",\w+",",1"),"\w+,","1,"),"\w+","1") – Mr_P Nov 19 '20 at 14:40
2

You can try the following

=ArrayFormula(REGEXREPLACE($Q26:Q,"\w+","1"))

(Please adjust ranges to your needs)

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.