Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
trim data and search for text strings
I have id tags that when scanned turn up in excel in the following format
982 000140555698 or 982 000140555612 The actual id number is always numbers no letters has 16 spaces in the format 000 000000000000. Nearly always it turns up with the sign in front but not always. I use the following code to get rid of any excess text ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16) The target is dim as range I then have to use the id number to search through 2 columns of id numbers and find the associated rider team number. Column headings are like this Team number Rider1 Tag id Rider2 tag id 100 982 000140555698 982 000140555612 101 982 000140555816 982 000140555836 etc To find the address of the cell where the scanned id tag is I use Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) then to find the team number Team = Sheet22.Cells(RiderCell.Row, 1) This works well for the few scanned id tags that don't have the in front of the number but any id's that come in like 982 000140555698 then it doesn't work for some reason. I have checked the mid statement and it seems to be doing the job. Is there a way I can set this up so that it will work for both possible scanned inputs. Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
trim data and search for text strings
from
Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) to Set RiderCell = Sheet22.Range("b4:c804").Find(what:=Target, _ lookin:=xlValues,lookat:=xlpart) "NDBC" wrote: I have id tags that when scanned turn up in excel in the following format 982 000140555698 or 982 000140555612 The actual id number is always numbers no letters has 16 spaces in the format 000 000000000000. Nearly always it turns up with the sign in front but not always. I use the following code to get rid of any excess text ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16) The target is dim as range I then have to use the id number to search through 2 columns of id numbers and find the associated rider team number. Column headings are like this Team number Rider1 Tag id Rider2 tag id 100 982 000140555698 982 000140555612 101 982 000140555816 982 000140555836 etc To find the address of the cell where the scanned id tag is I use Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) then to find the team number Team = Sheet22.Cells(RiderCell.Row, 1) This works well for the few scanned id tags that don't have the in front of the number but any id's that come in like 982 000140555698 then it doesn't work for some reason. I have checked the mid statement and it seems to be doing the job. Is there a way I can set this up so that it will work for both possible scanned inputs. Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
trim data and search for text strings
Try the below
Set RiderCell = Sheet22.Range("b4:c804").Find(What:=Target, _ LookIn:=xlValues, LookAt:=xlPart) If this post helps click Yes --------------- Jacob Skaria "NDBC" wrote: I have id tags that when scanned turn up in excel in the following format 982 000140555698 or 982 000140555612 The actual id number is always numbers no letters has 16 spaces in the format 000 000000000000. Nearly always it turns up with the sign in front but not always. I use the following code to get rid of any excess text ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16) The target is dim as range I then have to use the id number to search through 2 columns of id numbers and find the associated rider team number. Column headings are like this Team number Rider1 Tag id Rider2 tag id 100 982 000140555698 982 000140555612 101 982 000140555816 982 000140555836 etc To find the address of the cell where the scanned id tag is I use Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) then to find the team number Team = Sheet22.Cells(RiderCell.Row, 1) This works well for the few scanned id tags that don't have the in front of the number but any id's that come in like 982 000140555698 then it doesn't work for some reason. I have checked the mid statement and it seems to be doing the job. Is there a way I can set this up so that it will work for both possible scanned inputs. Thanks |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
trim data and search for text strings
Joel,
Thanks for the help but it still didn't work. I put a break in my code just at the ridercell statement and funily enough trying to find the target value doesn't work. I should be trying to find the ID value after i have used the mid statement so this works Set RiderCell = Sheet22.Range("b4:c804").Find(what:=ID, _ lookin:=xlValues,lookat:=xlpart) Thanks. you got me there in the end anyway. "Joel" wrote: from Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) to "NDBC" wrote: I have id tags that when scanned turn up in excel in the following format 982 000140555698 or 982 000140555612 The actual id number is always numbers no letters has 16 spaces in the format 000 000000000000. Nearly always it turns up with the sign in front but not always. I use the following code to get rid of any excess text ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16) The target is dim as range I then have to use the id number to search through 2 columns of id numbers and find the associated rider team number. Column headings are like this Team number Rider1 Tag id Rider2 tag id 100 982 000140555698 982 000140555612 101 982 000140555816 982 000140555836 etc To find the address of the cell where the scanned id tag is I use Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) then to find the team number Team = Sheet22.Cells(RiderCell.Row, 1) This works well for the few scanned id tags that don't have the in front of the number but any id's that come in like 982 000140555698 then it doesn't work for some reason. I have checked the mid statement and it seems to be doing the job. Is there a way I can set this up so that it will work for both possible scanned inputs. Thanks |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
trim data and search for text strings
Just for your information, you can replace this line of code...
ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16) with this much simpler one... ID = Replace(Target, "", "") -- Rick (MVP - Excel) "NDBC" wrote in message ... Joel, Thanks for the help but it still didn't work. I put a break in my code just at the ridercell statement and funily enough trying to find the target value doesn't work. I should be trying to find the ID value after i have used the mid statement so this works Set RiderCell = Sheet22.Range("b4:c804").Find(what:=ID, _ lookin:=xlValues,lookat:=xlpart) Thanks. you got me there in the end anyway. "Joel" wrote: from Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) to "NDBC" wrote: I have id tags that when scanned turn up in excel in the following format 982 000140555698 or 982 000140555612 The actual id number is always numbers no letters has 16 spaces in the format 000 000000000000. Nearly always it turns up with the sign in front but not always. I use the following code to get rid of any excess text ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16) The target is dim as range I then have to use the id number to search through 2 columns of id numbers and find the associated rider team number. Column headings are like this Team number Rider1 Tag id Rider2 tag id 100 982 000140555698 982 000140555612 101 982 000140555816 982 000140555836 etc To find the address of the cell where the scanned id tag is I use Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) then to find the team number Team = Sheet22.Cells(RiderCell.Row, 1) This works well for the few scanned id tags that don't have the in front of the number but any id's that come in like 982 000140555698 then it doesn't work for some reason. I have checked the mid statement and it seems to be doing the job. Is there a way I can set this up so that it will work for both possible scanned inputs. Thanks |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
trim data and search for text strings
Thanks Rick, I didn't know that. I may not change it however as I am not 100%
sure that other characters may not turn up at either end of the tag number and I know that the mid statement will work as long as there is no other spaces in the ID. Once I have used the system for a while longer and I am more confident in the range of input I will change it though. Thanks "Rick Rothstein" wrote: Just for your information, you can replace this line of code... ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16) with this much simpler one... ID = Replace(Target, "", "") -- Rick (MVP - Excel) "NDBC" wrote in message ... Joel, Thanks for the help but it still didn't work. I put a break in my code just at the ridercell statement and funily enough trying to find the target value doesn't work. I should be trying to find the ID value after i have used the mid statement so this works Set RiderCell = Sheet22.Range("b4:c804").Find(what:=ID, _ lookin:=xlValues,lookat:=xlpart) Thanks. you got me there in the end anyway. "Joel" wrote: from Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) to "NDBC" wrote: I have id tags that when scanned turn up in excel in the following format 982 000140555698 or 982 000140555612 The actual id number is always numbers no letters has 16 spaces in the format 000 000000000000. Nearly always it turns up with the sign in front but not always. I use the following code to get rid of any excess text ID = Mid(Target, WorksheetFunction.Search(" ", Target) - 3, 16) The target is dim as range I then have to use the id number to search through 2 columns of id numbers and find the associated rider team number. Column headings are like this Team number Rider1 Tag id Rider2 tag id 100 982 000140555698 982 000140555612 101 982 000140555816 982 000140555836 etc To find the address of the cell where the scanned id tag is I use Set RiderCell = Sheet22.Range("b4:c804").Find(Target, , xlValues) then to find the team number Team = Sheet22.Cells(RiderCell.Row, 1) This works well for the few scanned id tags that don't have the in front of the number but any id's that come in like 982 000140555698 then it doesn't work for some reason. I have checked the mid statement and it seems to be doing the job. Is there a way I can set this up so that it will work for both possible scanned inputs. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I filter or search mulitple for text strings? | Excel Discussion (Misc queries) | |||
Search multiple strings in one text file | Excel Programming | |||
Fast way to search many cells by column for text strings | Excel Discussion (Misc queries) | |||
Search through individual data strings | Excel Programming | |||
Search and Replace text strings within Macros | Excel Programming |