Problem while using MID Function..

[rishabh_khanna]

Hi everyone,

Could you please lend some time to look into the below issue which I faced while using Mid function.

Please find below my dataset:-

Name+Surname+ID
Rishabh-khanna$017293
Shiv-Ramalingam$013553
Arjun-Subhramaniam$014532

My consultant asked to extract only surname from this column,So accordingly I tried by using Mid function, I was able to make it done at first cell, but in second cell it is showing some missing characters from surname as it exceeded the width of the character which I defined in the first cell.

So please help me to figure it out either by using any other function into it. Be it Find Function or any other function. But please don't tell tell it by Alt+D+E i.e delimiting cell.

Thank you all in advance!!

Rishabh khanna

[FDibbins]

Hi and welcome to the forum

Try this, copied down...
=MID(A1,SEARCH("-",A1,1)+1,SEARCH("$",A1,1)-SEARCH("-",A1,1)-1)

what this does, is start the "return by searching at 1 charactre past -
SEARCH("-",A1,1)+1
then tests to see how many characters we need, by calc'ing the difference of characters betweem $ and -...
SEARCH("$",A1,1)-SEARCH("-",A1,1)-1

Hope that helps?

[Ghozi Alkatiri]

=mid(left(a1,find("$",a1)-1),find("-",a1)+1,len(a1))

[azumi]

Deleted Post
Azumi

[AlKey]

Or this

=TRIM(MID(SUBSTITUTE(REPLACE(A1,FIND("$",A1),20,""),"-",REPT(" ",255)),255,255))

And this

=REPLACE(REPLACE(A1,FIND("$",A1),20,""),1,FIND("-",A1),"")

[rishabh_khanna]

HI all,

Thanks for your support,, nice effort done.
@alkey u are great ..

[AlKey]

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).