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.

 

[sandhya16]

UPDATE - Also if there is no underscore in the string then return the original string-

shi007yyyy_1_2
ggfdf_dsdjskhd777_v_2
hdjshdjs_1_kkkkkkkkds
hdsiioooo

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

 

[sandhya16]

Please help me with this.

Thanks

 

[Wintio]

Hi,

Can you simply use Text to Columns (under the Data tab) select "Delimited" not "fixed width" and then select "other" and put an underscore in the box.

Then after 4 or so columns, whichever is free of data, you can fill an equal number of columns with len(A1), len(B1)... etc.

Finally in the next free column you can use '=INDEX(A1:D1,MATCH(MAX(F1:I1),F1:I1,0))

That should work as a quick fix.

 

[mikerickson]

If there are 9 or fewer _ in the string, you could use

=MAX(FIND("~",SUBSTITUTE("_"&A1&"_","_","~",{2;3;4;5;6;7;8;9;10})&"~")-FIND("~",SUBSTITUTE("_"&A1&"_","_","~",{1;2;3;4;5;6;7;8;9})&"~"))-1

 

[István Hirsch]

Try this UDF:

Function Getlong(s As String) As String
s = "_" & s & "_"
x = Split(s, "_")
y = x(0)
Getlong = x(0)
For i = 1 To UBound(x) - 1
    If (Len(x(i)) > Len(y)) Then y = x(i)
Next
Getlong = y
End Function

 

[Domenic]

Mike has given you a nice formula. I couldn't resist, though, I think it can be shortened to...

=MAX(MMULT(FIND("~",SUBSTITUTE("_"&A1&"_","_","~",{2;3;4;5;6;7;8;9;10}-{0,1})&"~")*{1,-1},{1;1}))-1

 

[István Hirsch]

Mike has given you a nice formula. I couldn't resist, though, I think it can be shortened to...

=MAX(MMULT(FIND("~",SUBSTITUTE("_"&A1&"_","_","~",{2;3;4;5;6;7;8;9;10}-{0,1})&"~")*{1,-1},{1;1}))-1

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

 

[Rick Rothstein]

Try this UDF:

Function Getlong(s As String) As String
s = "_" & s & "_"
x = Split(s, "_")
y = x(0)
Getlong = x(0)
For i = 1 To UBound(x) - 1
    If (Len(x(i)) > Len(y)) Then y = x(i)
Next
Getlong = y
End Function

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

 

[Domenic]

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

Yes, you're quite right. Thanks for pointing it out.