Using RANDBETWEEN with Non-Numeric Character strings To Generate a Result

[jworkman7]

Cells AE2 thru AH2 have the following characters:

AE2: COO1536578
AF2: AFI58491599
AG2: GHU4582154
AH2: UHT7851596

In AB2, I'd like to write a formula similar to the below, which will choose one of these cells at random and populate it in AB2. I currently have a working formula as follows, which works if the cells are numeric, but now need one for non-numeric:

=IFERROR(INDEX(Sheet1!$AE2:$AH2,1, RANDBETWEEN(1,COUNTIF(Sheet1!$AE2:$AH2,">0"))),0)

 

[Jonmo1]

Try changing
COUNTIF(Sheet1!$AE2:$AH2,">0")
to
COUNTA(Sheet1!$AE2:$AH2)

 

[jworkman7]

Try changing
COUNTIF(Sheet1!$AE2:$AH2,">0")
to
COUNTA(Sheet1!$AE2:$AH2)

Hey Jonmo, this worked yes, with one exception... if any of the four cells in my target range are blank, how can I ignore them in generating the random selection? As it happens, some of the time, two or three out of the four cells in the range are blank, or result in "" because they are errors

 

[Jonmo1]

OK, so they're not really blank. They contain formulas returning ""

Try
COUNTIF(Sheet1!$AE2:$AH2,"?*")

 

[jworkman7]

Yes! Works just like i need it to. Thank you very much!

 

[Jonmo1]

You're welcome.