RIGHT Function

[Guest]

Hi there,

I have a worksheet I have a right function which looks up the last 5 digits
in a cell. These digits represent the inventory code for individual inventory
items.

My problem is that sometimes these inventory codes are 6 digits long and
sometimes they are 5.

I can look up the last 6 digits and get a 0 infront of the 5 digit codes,
but I want to compare the data to data from another computer program which
won't work if the codes aren't represented in the same way. Can anyone think
of a way to look up the last 5 or 6 digits? Could this be done with an IF
combined with a RIGHT?

Cheers,

Sue

 

[Biff]

Hi!

What type of characters make up the entire string of
inventory codes?

Can you post a good representative sample (several) of
what these codes look like?

Biff

 

[Guest]

Thanks Biff,

The data from one program comes through as follows:

89101
123456

From the program that contains the ID's I use the right function on they
come through as follow with the last 6 digits representing the inventory code

999-000-00089101
232-987-00123456

The right function column returns me similar numbers to that from the first
database, but to get all codes I have to lookup 6 numbers which means my 5
digit codes have a 0 in front which I haven't found a way of getting rid of.

I couldn't figure out a way of formatting this, and drew a blank on other
ways around it.Any ideas for things to try out?

Cheers,

Sue

 

[Fred Smith]

Why not add a zero to all five digit numbers? Then you are always dealing
with 6 character fields.

Something like: =if(len(a1)=5,"0"&a1,a1)

 

[Ron Rosenfeld]

Thanks Biff,

The data from one program comes through as follows:

89101
123456

From the program that contains the ID's I use the right function on they
come through as follow with the last 6 digits representing the inventory code

999-000-00089101
232-987-00123456

The right function column returns me similar numbers to that from the first
database, but to get all codes I have to lookup 6 numbers which means my 5
digit codes have a 0 in front which I haven't found a way of getting rid of.

I couldn't figure out a way of formatting this, and drew a blank on other
ways around it.Any ideas for things to try out?

Cheers,

Sue

I'm not sure if you want the zero or don't want the zero.

If you merely want to compare the last six digits of each code, and if the code
from the one program is in A1 (with the 'string' code in B1), then:

=-RIGHT(B1,6)=-A1

will give TRUE if the last six digits are the same.

The (-) coerces both to be numbers; although both negative, it really doesn't
matter for a comparison.


--ron

 

[JulieD]

Hi

yes, you could use an IF if there is some way of consistent distinguishing
between the 5 and 6 digit codes ... if it is a space (and there is only one
space in the cell you're doing the right function on) then use
=IF(LEN(A1)-SEARCH(" ",A1,1)=6,RIGHT(A1,6),RIGHT(A1,5))
otherwise, please advise as to the distinguishing factor or provide a
representative sample of the data.

Regards
JulieD

 

[Arvi Laanemets]

Hi

You can lookup for value RIGHT(ImportedString,6)*1
I.e.
=RIGHT("999-000-00089101",6)*1 returns a number 89101

When 5-6 number ID's are strings too, then you can lookup for string value
RIGHT(ImportedString,6-(LEFT(RIGHT(ImportedString,6),1)="0"))


Arvi Laanemets

 

[Biff]

Hi!

I see you've got several replies but here's another option.

If your codes are always the same length:

999-000-00089101 = 16
232-987-00123456 = 16

AND, if the portion of the code you need to extract NEVER
starts with 0:

=IF(--MID(A1,11,1)=0,RIGHT(A1,5),RIGHT(A1,6))

Returns:

999-000-00089101 = 89101
232-987-00123456 = 123456

Biff

-----Original Message-----
Thanks Biff,

The data from one program comes through as follows:

89101
123456

From the program that contains the ID's I use the right function on they
come through as follow with the last 6 digits

representing the inventory code

 

[Ron Rosenfeld]

Hi!

I see you've got several replies but here's another option.

If your codes are always the same length:

999-000-00089101 = 16
232-987-00123456 = 16

AND, if the portion of the code you need to extract NEVER
starts with 0:

=IF(--MID(A1,11,1)=0,RIGHT(A1,5),RIGHT(A1,6))

Returns:

999-000-00089101 = 89101
232-987-00123456 = 123456

Biff

=--right(A1,6) would be simpler, and give the same result.


--ron