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

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

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

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

"Biff" wrote:

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

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

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)

--
Regards,
Fred
Please reply to newsgroup, not e-mail


"SiouxieQ" wrote in 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

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

"Biff" wrote:

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

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

On Sun, 7 Nov 2004 01:00:01 -0800, "SiouxieQ"
wrote:

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

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


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

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


"SiouxieQ" wrote in 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

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

"Biff" wrote:

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

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

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

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

"Biff" wrote:

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

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


.

On Sun, 7 Nov 2004 14:37:39 -0800, "Biff" wrote:

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