Formula to Truncate and Transpose

Hello:

Please refer to attached file.
I have Names in cell D2:D17 as shown.

I need formula to truncate the names from second "_" until third "_" and get the names in cell E2:E17.
Once the list is ready, i need to transpose the list and paste at at cell E25 as shown.

Let me know if you have any questions.
Thanks.
Riz

In E2

=TRIM(MID(SUBSTITUTE(D2,"_",REPT(" ",250)),501,250))

Copy down

Select E2 range, copy then paste/transpose in E25

As all the entries begin with the same 8 characters, you can use this in E2:

=MID(D2,9,FIND("_",D2,9)-9)

then copy down as required.

Hope this helps.

Pete

=trim(mid(substitute(substitute(d2,"_"," ")," ",rept(" ",len(d2))),len(d2)*2+1,len(d2)))

Hi John and FDibbins:

Thanks a lot, works

Riz

Hi Pete:

Your solution also works...Great

Riz

Hi Riz,

glad to help - thanks for the rep.

If you just wanted to end up with the names transposed into E25 across, you can put this formula in E25:

=MID(INDEX($D:$D,COLUMNS($D:E)),9,FIND("_",INDEX($D:$D,COLUMNS($D:E)),9)-9)

then copy across as required.

Hope this helps.

Pete