f the 6th digit from the right is "R", return Right(5), if not return right(3)

[bluepenguin3]

Hi,

I have a column of sample names that are a mixture of letter, numbers, and hyphens. I want to create another column that will produce a shortened version of that name.

Here is the basic premise of what I want from excel:

If the 6th digit from the right is "R", return Right(5), if not, return right(3).

Here are the results I want from this formula:

Sample Name                          Vial Name
11-2345 RG-3Z4                        RG-3Z4
30456-154-YRF RG-PP1                    RG-PP1
131-2345-ZJQ                            ZJQ
122-2345 RG-3Z4CT4VF5Q01               Q01

I've got the IF formula working fine, but I can't figure out how to make the first bit of the formula work, because I need to work from the right of the string, rather than from the left. Mid doesn't work, because I'm moving from the right, and there isn't a consistent # of characters working from the left.

The sample names are all different lengths and "R" can appear throughout the name, but I only care if "R" is the 6th digit from the right. So, that makes me think that search or find won't work either.

What do you think? Thanks!

 

[John Jairo V]

Hi!

This could be an option:
=RIGHT(A2,3+3*(MID(A2,LEN(A2)-5,1)="R"))

Or:
=RIGHT(A2,3+3*COUNTIF(A2,"*R?????"))

Blessings!

 

[bluepenguin3]

Thanks, John! The first formula worked perfectly for me.