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
---------------
Jacob Skaria


"jkramos2005" wrote:

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!

jkramos2005 wrote:
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)

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
---------------
Jacob Skaria


"jkramos2005" wrote:

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!

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



"jkramos2005" wrote:

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!

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



"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"jkramos2005" wrote:

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!

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

"jkramos2005" wrote in message
...
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!

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..
"Jacob Skaria" wrote:

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
---------------
Jacob Skaria


"jkramos2005" wrote:

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!