• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help with a formulae

S

Sai Prasad

New Member
Hi,

I have a list of data in the below format::(

Example:

acs.VSDF.com/Admin Accounts/Test/John Nair
acs.WER.com/Admin Accounts/Ram Charan
acs.VDS.com/Service Accounts/Test/Mac
acs.CAS.com/Admin Accounts/Test/jerry Clone
acs.ASD.com/Users/Raja
acs.KMN.com/Users/Phillip
acs.VFS.com/Users/Shiva
acs.EFS.com/Service Accounts/Ravi
acs.MLK.com/Service Accounts/Rahul
acs.OID.com/Service Accounts/Max
acs.AOD.com/Service Accounts/Mahesh
acs.QBC.com/Service Accounts/sandy
acs.ADC.com/Users/Monkeyman
acs.ABK.com/Service Accounts/Admin
ads.ABD.com/Users/Engineering/Users/Magala

Requirement: User name is mentioned in the last section where in the information is separated with "/". Could anyone help me with the formulae to get the data from the list.

Thanks in Advance,

Regards,
Sai.
 
Sai Prasad said:
Hi,

I have a list of data in the below format::(

Example:

acs.VSDF.com/Admin Accounts/Test/John Nair
acs.WER.com/Admin Accounts/Ram Charan
acs.VDS.com/Service Accounts/Test/Mac
acs.CAS.com/Admin Accounts/Test/jerry Clone
acs.ASD.com/Users/Raja
acs.KMN.com/Users/Phillip
acs.VFS.com/Users/Shiva
acs.EFS.com/Service Accounts/Ravi
acs.MLK.com/Service Accounts/Rahul
acs.OID.com/Service Accounts/Max
acs.AOD.com/Service Accounts/Mahesh
acs.QBC.com/Service Accounts/sandy
acs.ADC.com/Users/Monkeyman
acs.ABK.com/Service Accounts/Admin
ads.ABD.com/Users/Engineering/Users/Magala

Requirement: User name is mentioned in the last section where in the information is separated with "/". Could anyone help me with the formulae to get the data from the list.

Thanks in Advance,

Regards,
Sai.
Click to expand...
Hi,

With your first string in a1 try this and drag down as required.

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",256)),256))
 
Thanks @Narayank & @Mike for your help;
@santhurcv I am on MSOffice 2010
@Haseeb A I was looking for the names of the users in the list which were listed in the end..... Anyways Mike and Narayank formulae worked for me....thank you all for your help....:)
 
Sai Prasad said:
@Haseeb A I was looking for the names of the users in the list which were listed in the end...
Click to expand...

I'm not sure if you understood Haseeb's post, because you can actually use his method to get the list of names without any use of formulae, which is the ideal solution unless you want to retain the original text as well.

In case you don't know, the asterisk can be used to reference to any number of characters.

Using it like Haseeb's suggestion will make excel look for all the characters at the left and including the last "/" in the text string. So by then replacing that with nothing will transform your data in just the list of names you require.

Best thing about the asterisk is that you can even use it on formulae and filters, making it very useful when working with big databases.

Hope this helps you, cheers.
 
@Haseeb My Apologies, I was in a hurry initially as soon i got the result from Mike and Narayan.....thanks @Tiago MF for the explanation.....its simple and effective.....:rolleyes:
 
You must log in or register to reply here.
Back
Top