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

[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!

 

[Jacob Skaria]

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

 

[smartin]

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!

Assuming you want everything after the first dot,

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

 

[Jacob Skaria]

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

 

[Francis]

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

 

[Francis]

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

 

[Ashish Mathur]

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

 

[OKY]

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..