RIGHT_worksheet_function question
 

Hello -

I have an Excel spreadsheet that has 2 columns with many rows of
numeric data in them. The cells have been defined as text fields.

The problem I am having is I need to see if there is a way to express
the RIGHT_worksheet_function with some sort of dynamic variable. I
have a 5 digit number in many different rows of a particular column,
and need to remove the first 2 numbers in it so that the last 3 numbers
are all that is left in the field. The problem I am having is trying
to figure out how to do this without having to manually do it.

For example I have:

A B
19 19010
20 20010
21 21010
***NOTE*** Column A is already defined and only being used for example
purposes.

And so on...

Is there any way to make the expression work for column B without
having to change the expression's first 2 digits every time that
changes? Right now I have only been able to get "=RIGHT(19010,3)" to
work, but this will not longer work once the first 2 digits change in
the number. I need to figure out if there is a way to make this
formula dynamic for varying numbers.

The end result needs to be:

A B
19 010
20 010
21 010
***NOTE*** Column A is already defined and only being used for example
purposes.

Much thanks in advance...

First on column C type
=right(b1,len(a1))
Then copy paste special values and Ok will remove the formulas
Now you can cut and paste it over column B

However If the exact numbers on column A need to be replaced in column B
for instance in the example provided column A first row =19 and
corresponding value in column B also begins with 19 then
on column C type
=substitute(b1,a1,"")


wrote in message
oups.com...
Hello -

I have an Excel spreadsheet that has 2 columns with many rows of
numeric data in them. The cells have been defined as text fields.

The problem I am having is I need to see if there is a way to express
the RIGHT_worksheet_function with some sort of dynamic variable. I
have a 5 digit number in many different rows of a particular column,
and need to remove the first 2 numbers in it so that the last 3 numbers
are all that is left in the field. The problem I am having is trying
to figure out how to do this without having to manually do it.

For example I have:

A B
19 19010
20 20010
21 21010
***NOTE*** Column A is already defined and only being used for example
purposes.

And so on...

Is there any way to make the expression work for column B without
having to change the expression's first 2 digits every time that
changes? Right now I have only been able to get "=RIGHT(19010,3)" to
work, but this will not longer work once the first 2 digits change in
the number. I need to figure out if there is a way to make this
formula dynamic for varying numbers.

The end result needs to be:

A B
19 010
20 010
21 010
***NOTE*** Column A is already defined and only being used for example
purposes.

Much thanks in advance...

I obviously have scrambled brains today...My apologies, but I need the
last 3 numbers of any given 5 digit number using the
RIGHT_worksheet_function and this will not work with the numbers always
being different. Sorry for the confusion...I've been working with both
the LEFT and RIGHT expressions today and obviously was mixed up...


Thanks again, and any help would be much appreciated...

The first formula "RIGHT=(B#,len(A#)) almost has it. It only grabs the
last 2 digits of my 5 digit number though.

I got it! Here is what it took...

RIGHT=(B393,3)


This gave me the last 3 digits doing this from Column C. Thank you
very much for the info!

Have I really misunderstood your question?
=RIGHT(A1,3) will give 345 if A1 hold 12345 but it will be text\
=VALUE(RIGHT(A1,3)) will give 345 as number.
But so will =MOD(A1,1000)
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

wrote in message
oups.com...
I obviously have scrambled brains today...My apologies, but I need the
last 3 numbers of any given 5 digit number using the
RIGHT_worksheet_function and this will not work with the numbers always
being different. Sorry for the confusion...I've been working with both
the LEFT and RIGHT expressions today and obviously was mixed up...


Thanks again, and any help would be much appreciated...