Hi I am trying to make a formula where I can insert a period randomly throughout a set string of words. for example my word is "123 Apple Tree" and I want to be able to drag the column down and it will insert 2-4 periods randomly through out the text. "123 A.pp.le Tr.ee , 123 App.le T.re.e , 123 App.le Tre.e." do you know if this is possible thank you.
Hi James, you certainly could, though it would be a little messy: =SWITCH(RANDBETWEEN(1,4),1,REGEXREPLACE(A2,"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."), 2,REGEXREPLACE(REGEXREPLACE(A2,"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."), 3,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A2,"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."), 4,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A2,"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0.")) A much more elegant approach might be to use a custom function in Google Apps Script. yagisanatode.com/2018/08/24/google-apps-script-how-to-make-a-custom-function-to-use-in-google-sheets/ Here are a few other custom function examples: yagisanatode.com/category/code/google-apps-script/custom-functions/
Here is a link to and example sheet: You will need to change all cell references. docs.google.com/spreadsheets/d/1eXW_7_Zuhl7MFaDr_HyskhMgJKPiuROS04SDHV99Li0/edit?usp=sharing To make a copy to edit go to File > Make a copy. You will need to change all the cell values to the cell of text you want to change. (You can drag down all subsequent ones). Do do this quickly: - Ctrl + H for find and replace. - in Find, add : A2 -in Replace, add: your cell reference. -Change search to: specific range. -And select: Also search within formula. -Select : Replace all
Hi Elsa, Your best bet is to share the document (Big green button on the top right). This will send and email to the person informing them of their access. The shared document should maintain the page breaks for the the people you share it with. You can find out more about sharing here: ua-cam.com/video/g2v4HZsYARw/v-deo.html ~Yagi
Wouldn't think such a thing would be in a free web app. Google, you're scary.
Thx, for the help!:)
Hi I am trying to make a formula where I can insert a period randomly throughout a set string of words. for example my word is "123 Apple Tree" and I want to be able to drag the column down and it will insert 2-4 periods randomly through out the text. "123 A.pp.le Tr.ee , 123 App.le T.re.e , 123 App.le Tre.e." do you know if this is possible thank you.
Hi James, you certainly could, though it would be a little messy:
=SWITCH(RANDBETWEEN(1,4),1,REGEXREPLACE(A2,"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),
2,REGEXREPLACE(REGEXREPLACE(A2,"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),
3,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A2,"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),
4,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(A2,"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."),"^.{"&RANDBETWEEN(1,LEN(A2))&"}","$0."))
A much more elegant approach might be to use a custom function in Google Apps Script.
yagisanatode.com/2018/08/24/google-apps-script-how-to-make-a-custom-function-to-use-in-google-sheets/
Here are a few other custom function examples:
yagisanatode.com/category/code/google-apps-script/custom-functions/
@@yagisanatode hey thank you, i inserted the formula and i get the #REF! error. Not sure why.
Here is a link to and example sheet: You will need to change all cell references.
docs.google.com/spreadsheets/d/1eXW_7_Zuhl7MFaDr_HyskhMgJKPiuROS04SDHV99Li0/edit?usp=sharing
To make a copy to edit go to File > Make a copy.
You will need to change all the cell values to the cell of text you want to change. (You can drag down all subsequent ones).
Do do this quickly:
- Ctrl + H for find and replace.
- in Find, add : A2
-in Replace, add: your cell reference.
-Change search to: specific range.
-And select: Also search within formula.
-Select : Replace all
@@yagisanatode thank you very much, really appreciate it!
You're welcome.
~🐐
How could I send it by email to somebody once I’m done , before printing?
Hi Elsa,
Your best bet is to share the document (Big green button on the top right). This will send and email to the person informing them of their access. The shared document should maintain the page breaks for the the people you share it with.
You can find out more about sharing here: ua-cam.com/video/g2v4HZsYARw/v-deo.html
~Yagi