Extract last part of the string

[mrzoogle]

Hi Excel Guru's,


Hope you are all doing great!


I would like to know how can I extract the last part of this string.


The length of the string will vary and there could be unlimited "_" in the string.


134562_ AA (br rbg) - 4457658994 - uutouir2039845_W1_Q1_Y35_5590f


For some reason the formula below does not work on certain conditions.


=RIGHT(F9,LEN(F9)-LEN(SUBSTITUTE(F9,"_","")))


Always last four character from the last string are extracted, even if the last string is "55f" 3 character or "55555f" 6 characters.


If you could help me to come up with a solution that would be great.


Thanks for your time and looking forward to hear from you soon.


Kind Regards,


Z.

 

[Hui]

Z.


You haven't told us what the rule for determining how many characters to extract are ?


I suspect that you want 5590f

which is achieved by using :

=RIGHT(F9,LEN(F9)-LEN(SUBSTITUTE(F9,"_","")))


If this doesn't work, can you post a sample of text in which it doesn't work and explain what part you want to retrieve

 

[mrzoogle]

Sorry Hui, my apologies. I want to extract the last part of the string after last "_". So in this case that would be "5590f".


So the problem with my current formula is that, in the string below


134562_ AA (br rbg) - 4457658994 - uutouir2039845_W1_Q1_Y35_553390f


The result I want is "553390f" but my formulae give me the result of "3390f"


Thanks.

 

[NARAYANK991]

Hi ,


Try this :


=IFERROR(RIGHT(F9,LEN(F9)-FIND(CHAR(9),SUBSTITUTE(F9,"_",CHAR(9),LEN(F9)-LEN(SUBSTITUTE(F9,"_",""))))),F9)


Narayan

 

[mrzoogle]

You are star Narayan. Thanks for this formula, it works like a charm!


If you could also explain how formula works that would be awesome


Thanks both to Hui & Narayan for taking your time to help me with this.


Kind Regards,


Z.

 

[NARAYANK991]

Hi ,


Basically , the construct :


LEN(F9)-LEN(SUBSTITUTE(F9,"_",""))


is used to find out the number of underscores in the text in cell F9.


Let us assume there are 5 underscores in the text ; what we do now , is substitute the last underscore by a special character , in this case CHAR(9) , so that we can now look for this special character. This is because there is no simple way to look for the 5th underscore.


The SUBSTITUTE function has a 4th parameter called "instance num" ; once we find out there are 5 underscores we replace the 5th underscore ; if we had started off with 3 underscores , we would have replaced the 3rd underscore with CHAR(9).


The IFERROR is just to return the original text if there is no underscore in the string.


Narayan

 

[mrzoogle]

Thanks Narayan.

 

[shrivallabha]

Does this work:

=TRIM(RIGHT(SUBSTITUTE(A1,"_",REPT(" ",100)),100))