Home |
Search |
Today's Posts |
#1
|
|||
|
|||
RIGHT Function
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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 . |
#4
|
|||
|
|||
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 . |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
#7
|
|||
|
|||
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 . |
#8
|
|||
|
|||
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 . . |
#9
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Averaging function | Excel Discussion (Misc queries) | |||
Function in XL or in VBA for XL that pulls numeric digits from a t | Excel Discussion (Misc queries) | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
need to save values from a function before it changes | Excel Worksheet Functions | |||
SUMIF(AND) FUNCTION | Excel Worksheet Functions |