Split text and combine it randomly (as function)

Dear Moosmahna, plz try with below formula

=SUBSTITUTE(A1,"car is","")

Originally Posted by moosmahna

Ok, I got it. But that is not useful. The split should work automatically. It is faster for me. Did you try the function?

its work with excel2007

How can I hide this error?

Conditional formatting, using a formula similar to ISERROR(B1), then format as white font on white background or something like that.

Is there a specific part of the algorithm that you have difficulty with? Here's how I see this happening.

1) Using your UDF, split the text into an array, like you are currently doing.
2) count how many actual words and how many error values. Likely some form of COUNTIF() or similar
3) Generate a list of random unique integers between 2 and # of words. This post at MrExcel has a few simple examples. I know there are other approaches around the internet: http://www.mrexcel.com/forum/excel-q...m-numbers.html
4) Arrange array from 1 into new order as determined in 3. INDEX() function with array from 1 and the list of random numbers generated in 3.
5) Combine array from 4 into a single text string. TEXTJOIN() function.