Can I use an "If, then" function here...

  • Thread starter Thread starter jkramos2005
  • Start date Start date
J

jkramos2005

I have a range of accounts:

022.9700
000.4500
001.9600
r100.R9500


For those with no "r" I want to use a function of:

=right(cell location, count four characters from the right)


So for the first line the value returned would be:

9700

But for the fourth line I would like the value returned to be:

R9500

How would I adjust my formula so that I just use one formula for the whole
range of data?

Any help would be most appreciated.

Thanks!
 
Try the below formula in B1 with your entry in A1.

=RIGHT(A1,IF(LEFT(A1,1)="R",5,4))

If this post helps click Yes
 
If the number of numerics after the dot is not fixed; and if the text string
always contain only one dot. you can use this..which will extract the
characters after the first dot.

=MID(A1,FIND(".",A1)+1,LEN(A1))

If this post helps click Yes
 
one way

=MID(SUBSTITUTE(A1,"r",""),5,5)
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
Hi

This is a more robust formula
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another
 
Hi,

If there is just one decimal and you want to extract all characters after
the decimal, then you can use this

=TRIM(RIGHT(SUBSTITUTE(B4,".",REPT(" ",20)),10)), where B4 holds the string

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com
 
Hi Jacb.
I saw your post and I think I need your help with a similar situation.
I have a sheet, where I have
Technician# Job# TIME STATUS
Peter 1R 8-11am CP
2R 8-11am
Mike 5R 2-4pm CP
7R 2-5pm
11R 8-11am
I need to create sort of a report that will display only the jobs where the
STATUS is blank. Example:
TECH TIME JOB#
Peter 2-4pm 2R
Mike 2-5pm 7R
8-11am 11R
Could you help me with this situation?
Please be very specific in the procedure, Im not expert.

Thank You..
 
Back
Top