| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Text Compare Function
I think there's a function that compares two text values and determines if
they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
A few more examples of what you consider to be matches would be helpful.
*********** Regards, Ron XL2002, WinXP "JimK" wrote: I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
On Wed, 7 Jun 2006 12:47:02 -0700, JimK wrote:
I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. You could use the FIND function to determine if the shorter text string is contained within the longer text string. Is that what you want? --ron |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
Thanks Ron. Somethings like:
LAC vs. 2LAC FULT vs. FULTUS DB vs. DBK Thinks like that? "Ron Coderre" wrote: A few more examples of what you consider to be matches would be helpful. *********** Regards, Ron XL2002, WinXP "JimK" wrote: I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
Here are a couple options.....
For a value in A1 to find in B1 This formula matches TEXT entries in B1 (12ABC, xyz, etc): C1: =COUNTIF(B1,"*"&A1&"*")0 OR This formula matches any kind of entry in B1 (56, abc, a99b, etc) C1: =ISNUMBER(SEARCH(A1,B1)) Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thanks Ron. Somethings like: LAC vs. 2LAC FULT vs. FULTUS DB vs. DBK Thinks like that? "Ron Coderre" wrote: A few more examples of what you consider to be matches would be helpful. *********** Regards, Ron XL2002, WinXP "JimK" wrote: I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
Thank you Ron. I tried this, but it basically indicated a match if ANY
letter was in common with the two cells. I'd like to see if the text string in A1 matches to any part of the text string in B1. Which is why I want ABC & 2ABC to be a match, and ABC & ABCUS to be a match. However, I would not want "ABC" and "A Contracting Services" to match. Basically, MATCH only if the entire text string from A1 matches some part of B1. "Ron Coderre" wrote: Here are a couple options..... For a value in A1 to find in B1 This formula matches TEXT entries in B1 (12ABC, xyz, etc): C1: =COUNTIF(B1,"*"&A1&"*")0 OR This formula matches any kind of entry in B1 (56, abc, a99b, etc) C1: =ISNUMBER(SEARCH(A1,B1)) Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thanks Ron. Somethings like: LAC vs. 2LAC FULT vs. FULTUS DB vs. DBK Thinks like that? "Ron Coderre" wrote: A few more examples of what you consider to be matches would be helpful. *********** Regards, Ron XL2002, WinXP "JimK" wrote: I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
Jim
Perhaps I'm missing something.... Here's a table of values and results using the various techniques: Whe COUNTIF refers to this formula in C1: =COUNTIF(B1,"*"&A1&"*")0 SEARCH refers to this formula in C1: =ISNUMBER(SEARCH(A1,B1)) FIND refers to this formula in C1: =ISNUMBER(FIND(A1,B1)) Col_A Col_B COUNTIF SEARCH FIND lac 2LAC TRUE TRUE FALSE FULT FULTUS TRUE TRUE TRUE DB DBK TRUE TRUE TRUE LAC LAWK FALSE FALSE FALSE FULT 3FULAT FALSE FALSE FALSE DB FALSE FALSE FALSE Which of those returned values is incorrect? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thank you Ron. I tried this, but it basically indicated a match if ANY letter was in common with the two cells. I'd like to see if the text string in A1 matches to any part of the text string in B1. Which is why I want ABC & 2ABC to be a match, and ABC & ABCUS to be a match. However, I would not want "ABC" and "A Contracting Services" to match. Basically, MATCH only if the entire text string from A1 matches some part of B1. "Ron Coderre" wrote: Here are a couple options..... For a value in A1 to find in B1 This formula matches TEXT entries in B1 (12ABC, xyz, etc): C1: =COUNTIF(B1,"*"&A1&"*")0 OR This formula matches any kind of entry in B1 (56, abc, a99b, etc) C1: =ISNUMBER(SEARCH(A1,B1)) Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thanks Ron. Somethings like: LAC vs. 2LAC FULT vs. FULTUS DB vs. DBK Thinks like that? "Ron Coderre" wrote: A few more examples of what you consider to be matches would be helpful. *********** Regards, Ron XL2002, WinXP "JimK" wrote: I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
Ron, I think this is getting me there. Thank you so much. The COUTNIF and
SEARCH functions seem to net the same (and correct) results. What is the difference between the two? The only problem I see is that if I had just the letter A in column A and then ABC in column B, then the formulas would indicate a match. But I don't think there's a way to protect against that. "Ron Coderre" wrote: Jim Perhaps I'm missing something.... Here's a table of values and results using the various techniques: Whe COUNTIF refers to this formula in C1: =COUNTIF(B1,"*"&A1&"*")0 SEARCH refers to this formula in C1: =ISNUMBER(SEARCH(A1,B1)) FIND refers to this formula in C1: =ISNUMBER(FIND(A1,B1)) Col_A Col_B COUNTIF SEARCH FIND lac 2LAC TRUE TRUE FALSE FULT FULTUS TRUE TRUE TRUE DB DBK TRUE TRUE TRUE LAC LAWK FALSE FALSE FALSE FULT 3FULAT FALSE FALSE FALSE DB FALSE FALSE FALSE Which of those returned values is incorrect? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thank you Ron. I tried this, but it basically indicated a match if ANY letter was in common with the two cells. I'd like to see if the text string in A1 matches to any part of the text string in B1. Which is why I want ABC & 2ABC to be a match, and ABC & ABCUS to be a match. However, I would not want "ABC" and "A Contracting Services" to match. Basically, MATCH only if the entire text string from A1 matches some part of B1. "Ron Coderre" wrote: Here are a couple options..... For a value in A1 to find in B1 This formula matches TEXT entries in B1 (12ABC, xyz, etc): C1: =COUNTIF(B1,"*"&A1&"*")0 OR This formula matches any kind of entry in B1 (56, abc, a99b, etc) C1: =ISNUMBER(SEARCH(A1,B1)) Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thanks Ron. Somethings like: LAC vs. 2LAC FULT vs. FULTUS DB vs. DBK Thinks like that? "Ron Coderre" wrote: A few more examples of what you consider to be matches would be helpful. *********** Regards, Ron XL2002, WinXP "JimK" wrote: I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
Jim
The difference between the COUNTIF and the SEARCH functions, in this case, is: The COUNTIF function won't find a proper match if the Col_B value is a number. The SEARCH function handles that case correctly Example: A1: 56 B1: 567 The COUNTIF version returns FALSE. The SEARCH version returns TRUE. Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Ron, I think this is getting me there. Thank you so much. The COUTNIF and SEARCH functions seem to net the same (and correct) results. What is the difference between the two? The only problem I see is that if I had just the letter A in column A and then ABC in column B, then the formulas would indicate a match. But I don't think there's a way to protect against that. "Ron Coderre" wrote: Jim Perhaps I'm missing something.... Here's a table of values and results using the various techniques: Whe COUNTIF refers to this formula in C1: =COUNTIF(B1,"*"&A1&"*")0 SEARCH refers to this formula in C1: =ISNUMBER(SEARCH(A1,B1)) FIND refers to this formula in C1: =ISNUMBER(FIND(A1,B1)) Col_A Col_B COUNTIF SEARCH FIND lac 2LAC TRUE TRUE FALSE FULT FULTUS TRUE TRUE TRUE DB DBK TRUE TRUE TRUE LAC LAWK FALSE FALSE FALSE FULT 3FULAT FALSE FALSE FALSE DB FALSE FALSE FALSE Which of those returned values is incorrect? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thank you Ron. I tried this, but it basically indicated a match if ANY letter was in common with the two cells. I'd like to see if the text string in A1 matches to any part of the text string in B1. Which is why I want ABC & 2ABC to be a match, and ABC & ABCUS to be a match. However, I would not want "ABC" and "A Contracting Services" to match. Basically, MATCH only if the entire text string from A1 matches some part of B1. "Ron Coderre" wrote: Here are a couple options..... For a value in A1 to find in B1 This formula matches TEXT entries in B1 (12ABC, xyz, etc): C1: =COUNTIF(B1,"*"&A1&"*")0 OR This formula matches any kind of entry in B1 (56, abc, a99b, etc) C1: =ISNUMBER(SEARCH(A1,B1)) Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thanks Ron. Somethings like: LAC vs. 2LAC FULT vs. FULTUS DB vs. DBK Thinks like that? "Ron Coderre" wrote: A few more examples of what you consider to be matches would be helpful. *********** Regards, Ron XL2002, WinXP "JimK" wrote: I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
Ron, you have been immensley helpful. Thanks very much for your time.
You've solved my problem for me. "Ron Coderre" wrote: Jim The difference between the COUNTIF and the SEARCH functions, in this case, is: The COUNTIF function won't find a proper match if the Col_B value is a number. The SEARCH function handles that case correctly Example: A1: 56 B1: 567 The COUNTIF version returns FALSE. The SEARCH version returns TRUE. Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Ron, I think this is getting me there. Thank you so much. The COUTNIF and SEARCH functions seem to net the same (and correct) results. What is the difference between the two? The only problem I see is that if I had just the letter A in column A and then ABC in column B, then the formulas would indicate a match. But I don't think there's a way to protect against that. "Ron Coderre" wrote: Jim Perhaps I'm missing something.... Here's a table of values and results using the various techniques: Whe COUNTIF refers to this formula in C1: =COUNTIF(B1,"*"&A1&"*")0 SEARCH refers to this formula in C1: =ISNUMBER(SEARCH(A1,B1)) FIND refers to this formula in C1: =ISNUMBER(FIND(A1,B1)) Col_A Col_B COUNTIF SEARCH FIND lac 2LAC TRUE TRUE FALSE FULT FULTUS TRUE TRUE TRUE DB DBK TRUE TRUE TRUE LAC LAWK FALSE FALSE FALSE FULT 3FULAT FALSE FALSE FALSE DB FALSE FALSE FALSE Which of those returned values is incorrect? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thank you Ron. I tried this, but it basically indicated a match if ANY letter was in common with the two cells. I'd like to see if the text string in A1 matches to any part of the text string in B1. Which is why I want ABC & 2ABC to be a match, and ABC & ABCUS to be a match. However, I would not want "ABC" and "A Contracting Services" to match. Basically, MATCH only if the entire text string from A1 matches some part of B1. "Ron Coderre" wrote: Here are a couple options..... For a value in A1 to find in B1 This formula matches TEXT entries in B1 (12ABC, xyz, etc): C1: =COUNTIF(B1,"*"&A1&"*")0 OR This formula matches any kind of entry in B1 (56, abc, a99b, etc) C1: =ISNUMBER(SEARCH(A1,B1)) Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thanks Ron. Somethings like: LAC vs. 2LAC FULT vs. FULTUS DB vs. DBK Thinks like that? "Ron Coderre" wrote: A few more examples of what you consider to be matches would be helpful. *********** Regards, Ron XL2002, WinXP "JimK" wrote: I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Text Compare Function
You're very welcome.
Thanks for the feedback; I'm glad that worked for you. *********** Regards, Ron XL2002, WinXP "JimK" wrote: Ron, you have been immensley helpful. Thanks very much for your time. You've solved my problem for me. "Ron Coderre" wrote: Jim The difference between the COUNTIF and the SEARCH functions, in this case, is: The COUNTIF function won't find a proper match if the Col_B value is a number. The SEARCH function handles that case correctly Example: A1: 56 B1: 567 The COUNTIF version returns FALSE. The SEARCH version returns TRUE. Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Ron, I think this is getting me there. Thank you so much. The COUTNIF and SEARCH functions seem to net the same (and correct) results. What is the difference between the two? The only problem I see is that if I had just the letter A in column A and then ABC in column B, then the formulas would indicate a match. But I don't think there's a way to protect against that. "Ron Coderre" wrote: Jim Perhaps I'm missing something.... Here's a table of values and results using the various techniques: Whe COUNTIF refers to this formula in C1: =COUNTIF(B1,"*"&A1&"*")0 SEARCH refers to this formula in C1: =ISNUMBER(SEARCH(A1,B1)) FIND refers to this formula in C1: =ISNUMBER(FIND(A1,B1)) Col_A Col_B COUNTIF SEARCH FIND lac 2LAC TRUE TRUE FALSE FULT FULTUS TRUE TRUE TRUE DB DBK TRUE TRUE TRUE LAC LAWK FALSE FALSE FALSE FULT 3FULAT FALSE FALSE FALSE DB FALSE FALSE FALSE Which of those returned values is incorrect? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thank you Ron. I tried this, but it basically indicated a match if ANY letter was in common with the two cells. I'd like to see if the text string in A1 matches to any part of the text string in B1. Which is why I want ABC & 2ABC to be a match, and ABC & ABCUS to be a match. However, I would not want "ABC" and "A Contracting Services" to match. Basically, MATCH only if the entire text string from A1 matches some part of B1. "Ron Coderre" wrote: Here are a couple options..... For a value in A1 to find in B1 This formula matches TEXT entries in B1 (12ABC, xyz, etc): C1: =COUNTIF(B1,"*"&A1&"*")0 OR This formula matches any kind of entry in B1 (56, abc, a99b, etc) C1: =ISNUMBER(SEARCH(A1,B1)) Does that help? *********** Regards, Ron XL2002, WinXP "JimK" wrote: Thanks Ron. Somethings like: LAC vs. 2LAC FULT vs. FULTUS DB vs. DBK Thinks like that? "Ron Coderre" wrote: A few more examples of what you consider to be matches would be helpful. *********** Regards, Ron XL2002, WinXP "JimK" wrote: I think there's a function that compares two text values and determines if they are "similar." For instance ABC would be considered a match with ZABC. Is anyone familiar with this function? Thanks in advance. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| numerical integration | Excel Discussion (Misc queries) | |||
| Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
| Text Function with Different Formatting for Number | Excel Discussion (Misc queries) | |||
| Formulas dealing with text data | Excel Worksheet Functions | |||
| How do I compare two columns on seperate sheets and replace text . | Excel Worksheet Functions | |||
Linear Mode
