My goal is to generate 10 random phone numbers for each area code:

In cell B3 I have the following formula:

=ARRAYFORMULA(ROUND(RANDARRAY(4,7)*9))

which is responsible for generating 7 numbers (excluding area code) between 0 and 9 for each row.

The full phone number should have 10 numbers in total (including area code)

I also want to concatenate each area code with the 7 random numbers and repeat each full number (which would be in one cell now) 10 times (10 random numbers for code 111, etc...).

I want the RANDARRAY function to stay dynamic while repeating I don't want to repeat a static number.

Any idea on how this might be done?

Thanks!

CodePudding user response：

try:

=ARRAYFORMULA(FLATTEN("("&A3:A6&")-"&REGEXEXTRACT(TEXT(
 RANDARRAY(4, 3)*10^20, "0"), "\d{7}")))

with secondary dash:

=ARRAYFORMULA(REGEXREPLACE(FLATTEN("("&A3:A6&")-"&REGEXEXTRACT(TEXT(
 RANDARRAY(4, 10)*10^20, "0"), "\d{7}")), "(. -\d{3})(\d{4}$)", "$1-$2"))