Right function variable length

  • Thread starter Thread starter lundquic
  • Start date Start date
L

lundquic

I have a column of data that looks like:

54432\27982\UNIV 20080701

I am looking to remove everything to the right of the second \. The length
of those charaters is variable. I know this is an easy one but I can not
seem to get it to work.

Thanks for the help

Mike
 
To extract everything to the right:
=RIGHT(A2,LEN(A2)-FIND("\",A2,FIND("\",A2)+1))
To get rid of everything to the right:
=LEFT(A2,FIND("\",A2,FIND("\",A2)+1))
 
Assuming there is *always* 2 \ in the string:

=MID(A1,FIND("\",A1,FIND("\",A1)+1)+1,255)
 
=RIGHT(RIGHT(A1,LEN(A1)-FIND("\",A1,1)),LEN(RIGHT(A1,LEN(A1)-FIND
("\",A1,1)))-FIND("\",RIGHT(A1;LEN(A1)-FIND("\",A1,1)),1))

pls click YES if it helped you
 
Mike,

=LEFT(A2,FIND("\",A2,FIND("\",A2)+1)-1)

or

=LEFT(A2,FIND("\",A2,FIND("\",A2)+1))

depending on whether you want the final \ or not.

HTH,
Bernie
MS Excel MVP
 
I have a column of data that looks like:

54432\27982\UNIV 20080701

I am looking to remove everything to the right of the second \. The length
of those charaters is variable. I know this is an easy one but I can not
seem to get it to work.

Thanks for the help

Mike
Click to expand...

Will the 2nd "\" always be the last "\"? If so, try:

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",99)),99))

--ron
 
Back
Top