VBA function InSTR - How to use asterisk (as any other charakter) in searched phrase?
In Excel when we try to find some phrase we can put asterisk * inside as any other character. But how to do it inside VBA macro? For example below;
I want to find the secName by searching the value of firName with asterisk but id doesn't work. I suppose the problem is that VBA thinks that i want to find exactly "*" as normal character instead of anything.
Dim firName, secName As String
firName = "Da*"
secName = "Daniel"
search = InStr(1, secName, firName, vbTextCompare)
MsgBox (search)
Is it possible to use asterisk "*" in the way I described?
Regards
excel vba excel-vba asterisk
asked Nov 25 '18 at 22:49
Slow_Sheep_Slow_Sheep_
1
add a comment |
In Excel when we try to find some phrase we can put asterisk * inside as any other character. But how to do it inside VBA macro? For example below;
I want to find the secName by searching the value of firName with asterisk but id doesn't work. I suppose the problem is that VBA thinks that i want to find exactly "*" as normal character instead of anything.
Dim firName, secName As String
firName = "Da*"
secName = "Daniel"
search = InStr(1, secName, firName, vbTextCompare)
MsgBox (search)
Is it possible to use asterisk "*" in the way I described?
Regards
excel vba excel-vba asterisk
asked Nov 25 '18 at 22:49
Slow_Sheep_Slow_Sheep_
1
7
UseLike, or a regular expression.Instr()doesn't take wildcards.
– John Coleman
Nov 25 '18 at 22:57
2
INSTR("DA")will return1(as wouldINSTR("DA*")should such a beast exist, which it doesn) so with your example, a wildcard is not needed.
– cybernetic.nomad
Nov 25 '18 at 23:35
2
Please not that if youDim firName, secName As Stringonly the second variable is declared asStringbut the first one asVariant. You need to specify a type for every variable in VBA:Dim firName As String, secName As String
– Pᴇʜ
Nov 26 '18 at 8:35
2
I think you've chosen a bad example. Can you provide an example that is more close to what you are actually trying to do?
– Pᴇʜ
Nov 26 '18 at 8:38
Actually I will have many Strings in one base which I will have to find in another base. The problem is some of them will be different in some ways (additional comas, dots or spaces). For example one String in the first base is "OFFICE CLUB, S.A." but in the second one "OFFICE CLUB SA" I would like to somehow use wildcards but according to John Coleman reply vba doesn't provide that solution. I can use "Like" but sometimes it will return more than one result which I want to avoid. @Peh thanks for that tip, I didn't know that.
– Slow_Sheep_
Nov 26 '18 at 11:29
add a comment |
In Excel when we try to find some phrase we can put asterisk * inside as any other character. But how to do it inside VBA macro? For example below;
I want to find the secName by searching the value of firName with asterisk but id doesn't work. I suppose the problem is that VBA thinks that i want to find exactly "*" as normal character instead of anything.
Dim firName, secName As String
firName = "Da*"
secName = "Daniel"
search = InStr(1, secName, firName, vbTextCompare)
MsgBox (search)
Is it possible to use asterisk "*" in the way I described?
Regards
excel vba excel-vba asterisk
asked Nov 25 '18 at 22:49
Slow_Sheep_Slow_Sheep_
1
In Excel when we try to find some phrase we can put asterisk * inside as any other character. But how to do it inside VBA macro? For example below;
I want to find the secName by searching the value of firName with asterisk but id doesn't work. I suppose the problem is that VBA thinks that i want to find exactly "*" as normal character instead of anything.
Dim firName, secName As String
firName = "Da*"
secName = "Daniel"
search = InStr(1, secName, firName, vbTextCompare)
MsgBox (search)
Is it possible to use asterisk "*" in the way I described?
Regards
excel vba excel-vba asterisk
excel vba excel-vba asterisk
asked Nov 25 '18 at 22:49
Slow_Sheep_Slow_Sheep_
1
asked Nov 25 '18 at 22:49
Slow_Sheep_Slow_Sheep_
1
asked Nov 25 '18 at 22:49
Slow_Sheep_Slow_Sheep_
1
asked Nov 25 '18 at 22:49
Slow_Sheep_Slow_Sheep_
1
asked Nov 25 '18 at 22:49
Slow_Sheep_Slow_Sheep_
1
1
7
UseLike, or a regular expression.Instr()doesn't take wildcards.
– John Coleman
Nov 25 '18 at 22:57
2
INSTR("DA")will return1(as wouldINSTR("DA*")should such a beast exist, which it doesn) so with your example, a wildcard is not needed.
– cybernetic.nomad
Nov 25 '18 at 23:35
2
Please not that if youDim firName, secName As Stringonly the second variable is declared asStringbut the first one asVariant. You need to specify a type for every variable in VBA:Dim firName As String, secName As String
– Pᴇʜ
Nov 26 '18 at 8:35
2
I think you've chosen a bad example. Can you provide an example that is more close to what you are actually trying to do?
– Pᴇʜ
Nov 26 '18 at 8:38
Actually I will have many Strings in one base which I will have to find in another base. The problem is some of them will be different in some ways (additional comas, dots or spaces). For example one String in the first base is "OFFICE CLUB, S.A." but in the second one "OFFICE CLUB SA" I would like to somehow use wildcards but according to John Coleman reply vba doesn't provide that solution. I can use "Like" but sometimes it will return more than one result which I want to avoid. @Peh thanks for that tip, I didn't know that.
– Slow_Sheep_
Nov 26 '18 at 11:29
add a comment |
7
UseLike, or a regular expression.Instr()doesn't take wildcards.
– John Coleman
Nov 25 '18 at 22:57
2
INSTR("DA")will return1(as wouldINSTR("DA*")should such a beast exist, which it doesn) so with your example, a wildcard is not needed.
– cybernetic.nomad
Nov 25 '18 at 23:35
2
Please not that if youDim firName, secName As Stringonly the second variable is declared asStringbut the first one asVariant. You need to specify a type for every variable in VBA:Dim firName As String, secName As String
– Pᴇʜ
Nov 26 '18 at 8:35
2
I think you've chosen a bad example. Can you provide an example that is more close to what you are actually trying to do?
– Pᴇʜ
Nov 26 '18 at 8:38
Actually I will have many Strings in one base which I will have to find in another base. The problem is some of them will be different in some ways (additional comas, dots or spaces). For example one String in the first base is "OFFICE CLUB, S.A." but in the second one "OFFICE CLUB SA" I would like to somehow use wildcards but according to John Coleman reply vba doesn't provide that solution. I can use "Like" but sometimes it will return more than one result which I want to avoid. @Peh thanks for that tip, I didn't know that.
– Slow_Sheep_
Nov 26 '18 at 11:29
7
7
Use
Like, or a regular expression. Instr() doesn't take wildcards.– John Coleman
Nov 25 '18 at 22:57
Use
Like, or a regular expression. Instr() doesn't take wildcards.– John Coleman
Nov 25 '18 at 22:57
2
2
INSTR("DA") will return 1 (as would INSTR("DA*") should such a beast exist, which it doesn) so with your example, a wildcard is not needed.– cybernetic.nomad
Nov 25 '18 at 23:35
INSTR("DA") will return 1 (as would INSTR("DA*") should such a beast exist, which it doesn) so with your example, a wildcard is not needed.– cybernetic.nomad
Nov 25 '18 at 23:35
2
2
Please not that if you
Dim firName, secName As String only the second variable is declared as String but the first one as Variant. You need to specify a type for every variable in VBA: Dim firName As String, secName As String– Pᴇʜ
Nov 26 '18 at 8:35
Please not that if you
Dim firName, secName As String only the second variable is declared as String but the first one as Variant. You need to specify a type for every variable in VBA: Dim firName As String, secName As String– Pᴇʜ
Nov 26 '18 at 8:35
2
2
I think you've chosen a bad example. Can you provide an example that is more close to what you are actually trying to do?
– Pᴇʜ
Nov 26 '18 at 8:38
I think you've chosen a bad example. Can you provide an example that is more close to what you are actually trying to do?
– Pᴇʜ
Nov 26 '18 at 8:38
Actually I will have many Strings in one base which I will have to find in another base. The problem is some of them will be different in some ways (additional comas, dots or spaces). For example one String in the first base is "OFFICE CLUB, S.A." but in the second one "OFFICE CLUB SA" I would like to somehow use wildcards but according to John Coleman reply vba doesn't provide that solution. I can use "Like" but sometimes it will return more than one result which I want to avoid. @Peh thanks for that tip, I didn't know that.
– Slow_Sheep_
Nov 26 '18 at 11:29
Actually I will have many Strings in one base which I will have to find in another base. The problem is some of them will be different in some ways (additional comas, dots or spaces). For example one String in the first base is "OFFICE CLUB, S.A." but in the second one "OFFICE CLUB SA" I would like to somehow use wildcards but according to John Coleman reply vba doesn't provide that solution. I can use "Like" but sometimes it will return more than one result which I want to avoid. @Peh thanks for that tip, I didn't know that.
– Slow_Sheep_
Nov 26 '18 at 11:29
add a comment |
1 Answer
1
active
oldest
votes
You can either do a FuzzySearch like: Matching similar but not exact text strings in Excel VBA projects, …
… or you can use the The Levenshtein Distance to find out how similar 2 strings are which is probably more accurate but needs O(n*m) time for calculation. So don't use it on very long strings.
Function Levenshtein(str1 As String, str2 As String) As Long
Dim arrLev As Variant, intLen1 As Long, intLen2 As Long, i As Long
Dim j As Long, arrStr1 As Variant, arrStr2 As Variant, intMini As Long
intLen1 = Len(str1)
ReDim arrStr1(intLen1 + 1)
intLen2 = Len(str2)
ReDim arrStr2(intLen2 + 1)
ReDim arrLev(intLen1 + 1, intLen2 + 1)
arrLev(0, 0) = 0
For i = 1 To intLen1
arrLev(i, 0) = i
arrStr1(i) = Mid(str1, i, 1)
Next i
For j = 1 To intLen2
arrLev(0, j) = j
arrStr2(j) = Mid(str2, j, 1)
Next j
For j = 1 To intLen2
For i = 1 To intLen1
If arrStr1(i) = arrStr2(j) Then
arrLev(i, j) = arrLev(i - 1, j - 1)
Else
intMini = arrLev(i - 1, j) 'deletion
If intMini > arrLev(i, j - 1) Then intMini = arrLev(i, j - 1) 'insertion
If intMini > arrLev(i - 1, j - 1) Then intMini = arrLev(i - 1, j - 1) 'deletion
arrLev(i, j) = intMini + 1
End If
Next i
Next j
Levenshtein = arrLev(intLen1, intLen2)
End Function
The smaller the returned number is the more similar are the strings.
For example:
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB SA") 'returns 3
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB S.A.") 'returns 1
The second strings are more similar than the first ones.
answered Nov 27 '18 at 7:18
PᴇʜPᴇʜ
24.4k63052
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53472785%2fvba-function-instr-how-to-use-asterisk-as-any-other-charakter-in-searched-ph%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can either do a FuzzySearch like: Matching similar but not exact text strings in Excel VBA projects, …
… or you can use the The Levenshtein Distance to find out how similar 2 strings are which is probably more accurate but needs O(n*m) time for calculation. So don't use it on very long strings.
Function Levenshtein(str1 As String, str2 As String) As Long
Dim arrLev As Variant, intLen1 As Long, intLen2 As Long, i As Long
Dim j As Long, arrStr1 As Variant, arrStr2 As Variant, intMini As Long
intLen1 = Len(str1)
ReDim arrStr1(intLen1 + 1)
intLen2 = Len(str2)
ReDim arrStr2(intLen2 + 1)
ReDim arrLev(intLen1 + 1, intLen2 + 1)
arrLev(0, 0) = 0
For i = 1 To intLen1
arrLev(i, 0) = i
arrStr1(i) = Mid(str1, i, 1)
Next i
For j = 1 To intLen2
arrLev(0, j) = j
arrStr2(j) = Mid(str2, j, 1)
Next j
For j = 1 To intLen2
For i = 1 To intLen1
If arrStr1(i) = arrStr2(j) Then
arrLev(i, j) = arrLev(i - 1, j - 1)
Else
intMini = arrLev(i - 1, j) 'deletion
If intMini > arrLev(i, j - 1) Then intMini = arrLev(i, j - 1) 'insertion
If intMini > arrLev(i - 1, j - 1) Then intMini = arrLev(i - 1, j - 1) 'deletion
arrLev(i, j) = intMini + 1
End If
Next i
Next j
Levenshtein = arrLev(intLen1, intLen2)
End Function
The smaller the returned number is the more similar are the strings.
For example:
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB SA") 'returns 3
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB S.A.") 'returns 1
The second strings are more similar than the first ones.
answered Nov 27 '18 at 7:18
PᴇʜPᴇʜ
24.4k63052
add a comment |
You can either do a FuzzySearch like: Matching similar but not exact text strings in Excel VBA projects, …
… or you can use the The Levenshtein Distance to find out how similar 2 strings are which is probably more accurate but needs O(n*m) time for calculation. So don't use it on very long strings.
Function Levenshtein(str1 As String, str2 As String) As Long
Dim arrLev As Variant, intLen1 As Long, intLen2 As Long, i As Long
Dim j As Long, arrStr1 As Variant, arrStr2 As Variant, intMini As Long
intLen1 = Len(str1)
ReDim arrStr1(intLen1 + 1)
intLen2 = Len(str2)
ReDim arrStr2(intLen2 + 1)
ReDim arrLev(intLen1 + 1, intLen2 + 1)
arrLev(0, 0) = 0
For i = 1 To intLen1
arrLev(i, 0) = i
arrStr1(i) = Mid(str1, i, 1)
Next i
For j = 1 To intLen2
arrLev(0, j) = j
arrStr2(j) = Mid(str2, j, 1)
Next j
For j = 1 To intLen2
For i = 1 To intLen1
If arrStr1(i) = arrStr2(j) Then
arrLev(i, j) = arrLev(i - 1, j - 1)
Else
intMini = arrLev(i - 1, j) 'deletion
If intMini > arrLev(i, j - 1) Then intMini = arrLev(i, j - 1) 'insertion
If intMini > arrLev(i - 1, j - 1) Then intMini = arrLev(i - 1, j - 1) 'deletion
arrLev(i, j) = intMini + 1
End If
Next i
Next j
Levenshtein = arrLev(intLen1, intLen2)
End Function
The smaller the returned number is the more similar are the strings.
For example:
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB SA") 'returns 3
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB S.A.") 'returns 1
The second strings are more similar than the first ones.
answered Nov 27 '18 at 7:18
PᴇʜPᴇʜ
24.4k63052
add a comment |
You can either do a FuzzySearch like: Matching similar but not exact text strings in Excel VBA projects, …
… or you can use the The Levenshtein Distance to find out how similar 2 strings are which is probably more accurate but needs O(n*m) time for calculation. So don't use it on very long strings.
Function Levenshtein(str1 As String, str2 As String) As Long
Dim arrLev As Variant, intLen1 As Long, intLen2 As Long, i As Long
Dim j As Long, arrStr1 As Variant, arrStr2 As Variant, intMini As Long
intLen1 = Len(str1)
ReDim arrStr1(intLen1 + 1)
intLen2 = Len(str2)
ReDim arrStr2(intLen2 + 1)
ReDim arrLev(intLen1 + 1, intLen2 + 1)
arrLev(0, 0) = 0
For i = 1 To intLen1
arrLev(i, 0) = i
arrStr1(i) = Mid(str1, i, 1)
Next i
For j = 1 To intLen2
arrLev(0, j) = j
arrStr2(j) = Mid(str2, j, 1)
Next j
For j = 1 To intLen2
For i = 1 To intLen1
If arrStr1(i) = arrStr2(j) Then
arrLev(i, j) = arrLev(i - 1, j - 1)
Else
intMini = arrLev(i - 1, j) 'deletion
If intMini > arrLev(i, j - 1) Then intMini = arrLev(i, j - 1) 'insertion
If intMini > arrLev(i - 1, j - 1) Then intMini = arrLev(i - 1, j - 1) 'deletion
arrLev(i, j) = intMini + 1
End If
Next i
Next j
Levenshtein = arrLev(intLen1, intLen2)
End Function
The smaller the returned number is the more similar are the strings.
For example:
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB SA") 'returns 3
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB S.A.") 'returns 1
The second strings are more similar than the first ones.
answered Nov 27 '18 at 7:18
PᴇʜPᴇʜ
24.4k63052
You can either do a FuzzySearch like: Matching similar but not exact text strings in Excel VBA projects, …
… or you can use the The Levenshtein Distance to find out how similar 2 strings are which is probably more accurate but needs O(n*m) time for calculation. So don't use it on very long strings.
Function Levenshtein(str1 As String, str2 As String) As Long
Dim arrLev As Variant, intLen1 As Long, intLen2 As Long, i As Long
Dim j As Long, arrStr1 As Variant, arrStr2 As Variant, intMini As Long
intLen1 = Len(str1)
ReDim arrStr1(intLen1 + 1)
intLen2 = Len(str2)
ReDim arrStr2(intLen2 + 1)
ReDim arrLev(intLen1 + 1, intLen2 + 1)
arrLev(0, 0) = 0
For i = 1 To intLen1
arrLev(i, 0) = i
arrStr1(i) = Mid(str1, i, 1)
Next i
For j = 1 To intLen2
arrLev(0, j) = j
arrStr2(j) = Mid(str2, j, 1)
Next j
For j = 1 To intLen2
For i = 1 To intLen1
If arrStr1(i) = arrStr2(j) Then
arrLev(i, j) = arrLev(i - 1, j - 1)
Else
intMini = arrLev(i - 1, j) 'deletion
If intMini > arrLev(i, j - 1) Then intMini = arrLev(i, j - 1) 'insertion
If intMini > arrLev(i - 1, j - 1) Then intMini = arrLev(i - 1, j - 1) 'deletion
arrLev(i, j) = intMini + 1
End If
Next i
Next j
Levenshtein = arrLev(intLen1, intLen2)
End Function
The smaller the returned number is the more similar are the strings.
For example:
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB SA") 'returns 3
Debug.Print Levenshtein("OFFICE CLUB, S.A.", "OFFICE CLUB S.A.") 'returns 1
The second strings are more similar than the first ones.
answered Nov 27 '18 at 7:18
PᴇʜPᴇʜ
24.4k63052
answered Nov 27 '18 at 7:18
PᴇʜPᴇʜ
24.4k63052
answered Nov 27 '18 at 7:18
PᴇʜPᴇʜ
24.4k63052
answered Nov 27 '18 at 7:18
PᴇʜPᴇʜ
24.4k63052
24.4k63052
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53472785%2fvba-function-instr-how-to-use-asterisk-as-any-other-charakter-in-searched-ph%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
7
Use
Like, or a regular expression.Instr()doesn't take wildcards.– John Coleman
Nov 25 '18 at 22:57
2
INSTR("DA")will return1(as wouldINSTR("DA*")should such a beast exist, which it doesn) so with your example, a wildcard is not needed.– cybernetic.nomad
Nov 25 '18 at 23:35
2
Please not that if you
Dim firName, secName As Stringonly the second variable is declared asStringbut the first one asVariant. You need to specify a type for every variable in VBA:Dim firName As String, secName As String– Pᴇʜ
Nov 26 '18 at 8:35
2
I think you've chosen a bad example. Can you provide an example that is more close to what you are actually trying to do?
– Pᴇʜ
Nov 26 '18 at 8:38
Actually I will have many Strings in one base which I will have to find in another base. The problem is some of them will be different in some ways (additional comas, dots or spaces). For example one String in the first base is "OFFICE CLUB, S.A." but in the second one "OFFICE CLUB SA" I would like to somehow use wildcards but according to John Coleman reply vba doesn't provide that solution. I can use "Like" but sometimes it will return more than one result which I want to avoid. @Peh thanks for that tip, I didn't know that.
– Slow_Sheep_
Nov 26 '18 at 11:29