extract Maximum Length text from a Text String

[shukla.ankur281190]

Hi Masters.

"In this text string which text has maximum length"

Answer is maximum

I have built a formula which works good but it is too biggi...

B2=INDEX(INDEX(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),((ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))-1)*LEN(A1)+1,LEN(A1))),),MATCH(MAX(INDEX(LEN(INDEX(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),((ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))-1)*LEN(A1)+1,LEN(A1))),)),)),INDEX(LEN(INDEX(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",LEN(A1))),((ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1)))-1)*LEN(A1)+1,LEN(A1))),)),),0))

Any alternative will be great and it should also non array

[sktneer]

Why not use User Defined Function?

[shukla.ankur281190]

I used it

I would create a name Manger Name would be Array and refer to field paste below one.

=TRIM(MID(SUBSTITUTE(Sheet1!$A1," ",REPT(" ",LEN(Sheet1!$A1))),((ROW(INDIRECT("1:"&LEN(Sheet1!$A1)-LEN(SUBSTITUTE(Sheet1!$A1," ",""))+1)))-1)*LEN(Sheet1!$A1)+1,LEN(Sheet1!$A1)))

then

B2=INDEX(ArraY,MATCH(MAX(INDEX(LEN(ArraY),)),INDEX(LEN(ArraY),),0))

But need something more shorter

[Olly]

How do you want to handle if more than one word in the input string are the same maximum length?

e.g. "This is my example of a working formula" - the words example, working and formula are all 7 characters long. What is your required output?

[shukla.ankur281190]

Good questing Olly I did not thing about this lolx. This thread was raised by my colleague, I will ask him about this condition

[Olly]

Okay - here's a UDF which can handle extracting the first, last, or ALL longest words from an input string:

Please Login or Register  to view this content.

Use like this:

	A	B	C	D	E
1	Input	First	Last	Default Join	Custom Join
2	In this text string which text has maximum length	maximum	maximum	maximum	maximum
3	This is my example of a working formula	example	formula	example, working, formula	example | working | formula
4		=LongestWord(A3,1)	=LongestWord(A3,2)	=LongestWord(A3,0)	=LongestWord(A3,0,"" | "")

[shukla.ankur281190]

VBA is just amazing thank you olly for the code but I am hoping for formula solutions What alternative can be?

[tim201110]

helper columns/rows help a lot

[shukla.ankur281190]

This looks spooky TIM, but thanks for your time.