Looking for a formula to get biggest word between the underscores

[sandhya16]

I have data like -

shi007yyyy_1_2
ggfdf_dsdjskhd777_v_2
hdjshdjs_1_kkkkkkkkds


I am looking for a formula to get the biggest word out of the data-

So the answer i am looking for should be -
shi007yyyy
dsdjskhd777
kkkkkkkkds

Left,Right & mid works but i have lots of data and i cannot change the formula each time.
Please help.

 

[István Hirsch]

Your UDF can be write more compactly like this...

Function Getlong(s As String) As String
  For Each V In Split(s, "_")
    If Len(V) > Len(Getlong) Then Getlong = V
  Next
End Function

Thanks Rick, definitely more compact.

 

[sandhya16]

Thanks all .All of them worked perfectly fine.

Thanks Rick, definitely more compact.

 

[mikerickson]

I thought the OP needed the longest substring, not only its length.

Yes. My bad.