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...


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

  • =REGEXREPLACE(REGEXREPLACE(A1,",\w+",",1"),"\w+,","1,")
    – Oleg_S
    Nov 19, 2020 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, 2020 at 14:17
  • figured it out - thanks @Oleg_S ... =REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A1,",\w+",",1"),"\w+,","1,"),"\w+","1")
    – Mr_P
    Nov 19, 2020 at 14:40

1 Answer 1


You can try the following


(Please adjust ranges to your needs)

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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