Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match 'unmatching' search term with cell values
Hi once again people,
I am having problems getting a search feature to work in excel. At present it just matchs the exact strings passed into it, i know about the 'option compare text' statement so it recognises lower and uppercase letters. The problem is this, the info being searched is dragged off a mainframe and then formated once imported to excel via a macro, this presents a problem because large amounts of data are being moved at once it is very difficult to remove unwanted characters. Is there a way to build in a feature to my cade that would ignore misc characters such as, spaces .. , / \ ; : If these can be ignored and just the text itself in each cell is searched this would be a massive advantage, can anyone help??? PLEASE HELP ME!! The Noob. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match 'unmatching' search term with cell values
Why not just use Edit | Replace and remove all of the unwanted characters
in the data. (leave "replace with" blank) -- Jim Cone Portland, Oregon USA "Noob McKnownowt" wrote in message Hi once again people, I am having problems getting a search feature to work in excel. At present it just matchs the exact strings passed into it, i know about the 'option compare text' statement so it recognises lower and uppercase letters. The problem is this, the info being searched is dragged off a mainframe and then formated once imported to excel via a macro, this presents a problem because large amounts of data are being moved at once it is very difficult to remove unwanted characters. Is there a way to build in a feature to my cade that would ignore misc characters such as, spaces .. , / \ ; : If these can be ignored and just the text itself in each cell is searched this would be a massive advantage, can anyone help??? PLEASE HELP ME!! The Noob. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match 'unmatching' search term with cell values
Are you aware that Find (either from Excel's Edit/Find on the menu bar or
the VB function) will work with wild cards? Let's say you wanted to find "some text" and that the text you are searching has this in one of your cells... This cell contains some //,,.:: text in it. You can look for "some*text" and it will find the cell with the above text in it. Now, of course, because it is a wild card search, it will also find something like this... This is some of the text in the file If you are looking for the two words "some" and "text" next to each other with nothing between them (except possibly punctuation marks), then, in code, you can do something like this... Dim FoundCell As Range Set FoundCell = ActiveSheet.UsedRange.Find("some*text") If Not FoundCell Is Nothing Then If FoundCell.Value Like "*some*[A-Za-z0-9]*text*" Then MsgBox """some text"" does not exist together" Else MsgBox "I found ""some text""" End If End If You can, of course, expand this as necessary to find all cells and then do whatever you are going to do with them (if you are doing this and need help with it, explain what you need to do with the found cells). -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... Hi once again people, I am having problems getting a search feature to work in excel. At present it just matchs the exact strings passed into it, i know about the 'option compare text' statement so it recognises lower and uppercase letters. The problem is this, the info being searched is dragged off a mainframe and then formated once imported to excel via a macro, this presents a problem because large amounts of data are being moved at once it is very difficult to remove unwanted characters. Is there a way to build in a feature to my cade that would ignore misc characters such as, spaces . , / \ ; : If these can be ignored and just the text itself in each cell is searched this would be a massive advantage, can anyone help??? PLEASE HELP ME!! The Noob. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match 'unmatching' search term with cell values
"Rick Rothstein" wrote: Are you aware that Find (either from Excel's Edit/Find on the menu bar or the VB function) will work with wild cards? Let's say you wanted to find "some text" and that the text you are searching has this in one of your cells... This cell contains some //,,.:: text in it. You can look for "some*text" and it will find the cell with the above text in it. Now, of course, because it is a wild card search, it will also find something like this... This is some of the text in the file If you are looking for the two words "some" and "text" next to each other with nothing between them (except possibly punctuation marks), then, in code, you can do something like this... Dim FoundCell As Range Set FoundCell = ActiveSheet.UsedRange.Find("some*text") If Not FoundCell Is Nothing Then If FoundCell.Value Like "*some*[A-Za-z0-9]*text*" Then MsgBox """some text"" does not exist together" Else MsgBox "I found ""some text""" End If End If You can, of course, expand this as necessary to find all cells and then do whatever you are going to do with them (if you are doing this and need help with it, explain what you need to do with the found cells). -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... Hi once again people, I am having problems getting a search feature to work in excel. At present it just matchs the exact strings passed into it, i know about the 'option compare text' statement so it recognises lower and uppercase letters. The problem is this, the info being searched is dragged off a mainframe and then formated once imported to excel via a macro, this presents a problem because large amounts of data are being moved at once it is very difficult to remove unwanted characters. Is there a way to build in a feature to my cade that would ignore misc characters such as, spaces . , / \ ; : If these can be ignored and just the text itself in each cell is searched this would be a massive advantage, can anyone help??? PLEASE HELP ME!! The Noob. Hey guys, thanks for thr responce, and i am sorry it has taken so long for me to get back, but i havent been in work for the last couple of days. any way, the problem i am having is this: i work in a warehouse writting little pieces of VBA in excel that will allow a quick format of data and some calculations so that a report can be compiled and a summary of the info can be made. in this instance my problem is this, every day a variable amount of data is pulled off the companys mainframe, once this info is formatted (i have already done this) the user needs to find a collection of data about a particular product in any location in the warehouse quickly. the user needs to be able to search through many, many products and have the relevant info from all entries displayed, so if the data entry reads "NON-STOCK ITEM . . " then i would like the info returned if they enter "non stock item" or a variation of this. hope you understand what i am tryin to do if not then please feel free to ask a question. i was hoping the VB had some kind of function built in for this such as the option compare text, but apparently not. cheers. The Noob. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match 'unmatching' search term with cell values
Explain the " . . " you are showing in "NON-STOCK ITEM . . "
Does that mean there is other text after the words "NON-STOCK ITEM"? Also, I'm not exactly clear where or how you want to show the information that is found. Also, it would help if you gave us a clue as to your layout (what column is the search being performed on, what columns to you want to show when your search term is found, etc.) -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... "Rick Rothstein" wrote: Are you aware that Find (either from Excel's Edit/Find on the menu bar or the VB function) will work with wild cards? Let's say you wanted to find "some text" and that the text you are searching has this in one of your cells... This cell contains some //,,.:: text in it. You can look for "some*text" and it will find the cell with the above text in it. Now, of course, because it is a wild card search, it will also find something like this... This is some of the text in the file If you are looking for the two words "some" and "text" next to each other with nothing between them (except possibly punctuation marks), then, in code, you can do something like this... Dim FoundCell As Range Set FoundCell = ActiveSheet.UsedRange.Find("some*text") If Not FoundCell Is Nothing Then If FoundCell.Value Like "*some*[A-Za-z0-9]*text*" Then MsgBox """some text"" does not exist together" Else MsgBox "I found ""some text""" End If End If You can, of course, expand this as necessary to find all cells and then do whatever you are going to do with them (if you are doing this and need help with it, explain what you need to do with the found cells). -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... Hi once again people, I am having problems getting a search feature to work in excel. At present it just matchs the exact strings passed into it, i know about the 'option compare text' statement so it recognises lower and uppercase letters. The problem is this, the info being searched is dragged off a mainframe and then formated once imported to excel via a macro, this presents a problem because large amounts of data are being moved at once it is very difficult to remove unwanted characters. Is there a way to build in a feature to my cade that would ignore misc characters such as, spaces . , / \ ; : If these can be ignored and just the text itself in each cell is searched this would be a massive advantage, can anyone help??? PLEASE HELP ME!! The Noob. Hey guys, thanks for thr responce, and i am sorry it has taken so long for me to get back, but i havent been in work for the last couple of days. any way, the problem i am having is this: i work in a warehouse writting little pieces of VBA in excel that will allow a quick format of data and some calculations so that a report can be compiled and a summary of the info can be made. in this instance my problem is this, every day a variable amount of data is pulled off the companys mainframe, once this info is formatted (i have already done this) the user needs to find a collection of data about a particular product in any location in the warehouse quickly. the user needs to be able to search through many, many products and have the relevant info from all entries displayed, so if the data entry reads "NON-STOCK ITEM . . " then i would like the info returned if they enter "non stock item" or a variation of this. hope you understand what i am tryin to do if not then please feel free to ask a question. i was hoping the VB had some kind of function built in for this such as the option compare text, but apparently not. cheers. The Noob. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Match 'unmatching' search term with cell values
Hey Rick,
Thanks alot for your help pal, but i have found a solution to the problem, i tried posting a message to say so but for some reason it wouldnt come up as part of this thread. But once again thanks alot for your continued help, very much apprieciated. The Noob "Rick Rothstein" wrote: Explain the " . . " you are showing in "NON-STOCK ITEM . . " Does that mean there is other text after the words "NON-STOCK ITEM"? Also, I'm not exactly clear where or how you want to show the information that is found. Also, it would help if you gave us a clue as to your layout (what column is the search being performed on, what columns to you want to show when your search term is found, etc.) -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... "Rick Rothstein" wrote: Are you aware that Find (either from Excel's Edit/Find on the menu bar or the VB function) will work with wild cards? Let's say you wanted to find "some text" and that the text you are searching has this in one of your cells... This cell contains some //,,.:: text in it. You can look for "some*text" and it will find the cell with the above text in it. Now, of course, because it is a wild card search, it will also find something like this... This is some of the text in the file If you are looking for the two words "some" and "text" next to each other with nothing between them (except possibly punctuation marks), then, in code, you can do something like this... Dim FoundCell As Range Set FoundCell = ActiveSheet.UsedRange.Find("some*text") If Not FoundCell Is Nothing Then If FoundCell.Value Like "*some*[A-Za-z0-9]*text*" Then MsgBox """some text"" does not exist together" Else MsgBox "I found ""some text""" End If End If You can, of course, expand this as necessary to find all cells and then do whatever you are going to do with them (if you are doing this and need help with it, explain what you need to do with the found cells). -- Rick (MVP - Excel) "Noob McKnownowt" wrote in message ... Hi once again people, I am having problems getting a search feature to work in excel. At present it just matchs the exact strings passed into it, i know about the 'option compare text' statement so it recognises lower and uppercase letters. The problem is this, the info being searched is dragged off a mainframe and then formated once imported to excel via a macro, this presents a problem because large amounts of data are being moved at once it is very difficult to remove unwanted characters. Is there a way to build in a feature to my cade that would ignore misc characters such as, spaces . , / \ ; : If these can be ignored and just the text itself in each cell is searched this would be a massive advantage, can anyone help??? PLEASE HELP ME!! The Noob. Hey guys, thanks for thr responce, and i am sorry it has taken so long for me to get back, but i havent been in work for the last couple of days. any way, the problem i am having is this: i work in a warehouse writting little pieces of VBA in excel that will allow a quick format of data and some calculations so that a report can be compiled and a summary of the info can be made. in this instance my problem is this, every day a variable amount of data is pulled off the companys mainframe, once this info is formatted (i have already done this) the user needs to find a collection of data about a particular product in any location in the warehouse quickly. the user needs to be able to search through many, many products and have the relevant info from all entries displayed, so if the data entry reads "NON-STOCK ITEM . . " then i would like the info returned if they enter "non stock item" or a variation of this. hope you understand what i am tryin to do if not then please feel free to ask a question. i was hoping the VB had some kind of function built in for this such as the option compare text, but apparently not. cheers. The Noob. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search and Match Values | Excel Programming | |||
How to Restrict Forum Search to Exact Term | Excel Discussion (Misc queries) | |||
Search Column, If match found then add two values. | Excel Programming | |||
User-defined search term in macro | Excel Discussion (Misc queries) | |||
Formula to search for given term, and if not found in column to add it to list | Excel Worksheet Functions |