Mixing
How to find a cell that contains parenthesis around a number - e.g. (1)
The formula I'm using is:
=IF(SUM(COUNTIF(K6,"*"&{"current","(1)"}&"*")),"within 5 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(2)"}&"*")),"within 10 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(3)"}&"*")),"within 15 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(4)"}&"*")),"within 20 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(5)"}&"*")),"within 25 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(6)"}&"*")),"within 30 minutes"))))))
The output is pulling any cell that contains both current and 1/2/3 etc.
I need it to pull only cells that contain current and (1)/(2)/(3) etc.
Hoping there's an easy way to make sure the formula includes parentheses!
Here's an example screenshot of the expected outcome:
excel vba excel-vba user-defined-functions
edited Jan 3 at 17:30
Andreas
17.1k41844
asked Jan 2 at 20:42
maxmax
365
add a comment |
The formula I'm using is:
=IF(SUM(COUNTIF(K6,"*"&{"current","(1)"}&"*")),"within 5 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(2)"}&"*")),"within 10 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(3)"}&"*")),"within 15 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(4)"}&"*")),"within 20 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(5)"}&"*")),"within 25 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(6)"}&"*")),"within 30 minutes"))))))
The output is pulling any cell that contains both current and 1/2/3 etc.
I need it to pull only cells that contain current and (1)/(2)/(3) etc.
Hoping there's an easy way to make sure the formula includes parentheses!
Here's an example screenshot of the expected outcome:
excel vba excel-vba user-defined-functions
edited Jan 3 at 17:30
Andreas
17.1k41844
asked Jan 2 at 20:42
maxmax
365
Can you please edit your question to give us sample data?
– cybernetic.nomad
Jan 2 at 20:49
I cannot reproduce your issue. Do you mean "current AND one of the numbers" or "current OR either of the numbers"? Your logic is not clear. Also,(2)can be notation for-2, so depending on you cell contents it could be a number, not text. A data sample and/or a screenshot would help.
– teylyn
Jan 2 at 20:51
Added a sample table screenshot
– max
Jan 2 at 21:04
Use regex and UDF.
– Andreas
Jan 2 at 21:05
add a comment |
The formula I'm using is:
=IF(SUM(COUNTIF(K6,"*"&{"current","(1)"}&"*")),"within 5 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(2)"}&"*")),"within 10 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(3)"}&"*")),"within 15 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(4)"}&"*")),"within 20 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(5)"}&"*")),"within 25 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(6)"}&"*")),"within 30 minutes"))))))
The output is pulling any cell that contains both current and 1/2/3 etc.
I need it to pull only cells that contain current and (1)/(2)/(3) etc.
Hoping there's an easy way to make sure the formula includes parentheses!
Here's an example screenshot of the expected outcome:
excel vba excel-vba user-defined-functions
edited Jan 3 at 17:30
Andreas
17.1k41844
asked Jan 2 at 20:42
maxmax
365
The formula I'm using is:
=IF(SUM(COUNTIF(K6,"*"&{"current","(1)"}&"*")),"within 5 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(2)"}&"*")),"within 10 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(3)"}&"*")),"within 15 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(4)"}&"*")),"within 20 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(5)"}&"*")),"within 25 minutes",
IF(SUM(COUNTIF(K6,"*"&{"current","(6)"}&"*")),"within 30 minutes"))))))
The output is pulling any cell that contains both current and 1/2/3 etc.
I need it to pull only cells that contain current and (1)/(2)/(3) etc.
Hoping there's an easy way to make sure the formula includes parentheses!
Here's an example screenshot of the expected outcome:
excel vba excel-vba user-defined-functions
excel vba excel-vba user-defined-functions
edited Jan 3 at 17:30
Andreas
17.1k41844
asked Jan 2 at 20:42
maxmax
365
edited Jan 3 at 17:30
Andreas
17.1k41844
asked Jan 2 at 20:42
maxmax
365
edited Jan 3 at 17:30
Andreas
17.1k41844
edited Jan 3 at 17:30
Andreas
17.1k41844
edited Jan 3 at 17:30
Andreas
17.1k41844
17.1k41844
asked Jan 2 at 20:42
maxmax
365
asked Jan 2 at 20:42
maxmax
365
asked Jan 2 at 20:42
maxmax
365
365
Can you please edit your question to give us sample data?
– cybernetic.nomad
Jan 2 at 20:49
I cannot reproduce your issue. Do you mean "current AND one of the numbers" or "current OR either of the numbers"? Your logic is not clear. Also,(2)can be notation for-2, so depending on you cell contents it could be a number, not text. A data sample and/or a screenshot would help.
– teylyn
Jan 2 at 20:51
Added a sample table screenshot
– max
Jan 2 at 21:04
Use regex and UDF.
– Andreas
Jan 2 at 21:05
add a comment |
Can you please edit your question to give us sample data?
– cybernetic.nomad
Jan 2 at 20:49
I cannot reproduce your issue. Do you mean "current AND one of the numbers" or "current OR either of the numbers"? Your logic is not clear. Also,(2)can be notation for-2, so depending on you cell contents it could be a number, not text. A data sample and/or a screenshot would help.
– teylyn
Jan 2 at 20:51
Added a sample table screenshot
– max
Jan 2 at 21:04
Use regex and UDF.
– Andreas
Jan 2 at 21:05
Can you please edit your question to give us sample data?
– cybernetic.nomad
Jan 2 at 20:49
Can you please edit your question to give us sample data?
– cybernetic.nomad
Jan 2 at 20:49
I cannot reproduce your issue. Do you mean "current AND one of the numbers" or "current OR either of the numbers"? Your logic is not clear. Also,
(2) can be notation for -2, so depending on you cell contents it could be a number, not text. A data sample and/or a screenshot would help.– teylyn
Jan 2 at 20:51
I cannot reproduce your issue. Do you mean "current AND one of the numbers" or "current OR either of the numbers"? Your logic is not clear. Also,
(2) can be notation for -2, so depending on you cell contents it could be a number, not text. A data sample and/or a screenshot would help.– teylyn
Jan 2 at 20:51
Added a sample table screenshot
– max
Jan 2 at 21:04
Added a sample table screenshot
– max
Jan 2 at 21:04
Use regex and UDF.
– Andreas
Jan 2 at 21:05
Use regex and UDF.
– Andreas
Jan 2 at 21:05
add a comment |
2 Answers
2
active
oldest
votes
If VBA and UDF is ok then I advice you to use regex.
Open VBA editor (ALT +F11) and add a module.
Paste the following code and save the Excel workbook as macroactivated workbook (xlsm).
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*(current and (d+))"
' or if you want to match optional ()
'RE.Pattern = ".*(current and (?d+)?)"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = Matches.Item(0).submatches.Item(0)
End If
End Function
Use it as a formula like:
=Regex(A1)
It will return the part it's looking for current and [number]
The return of the code can of course be whatever you want.
But I don't understand the logic in your question that is why I return what it is looking for
I see now the logic.
This will return the output as you expect.
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current and ((d+))"
' or if you want to match optional ()
'RE.Pattern = ".*current and (?(d+))?"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
It multiplies the captured number with 5 to get the number of minutes.
If nothing is found then it returns False.
VBA and regex generally means the code in the worksheet is easier to maintain and easier to debug.
In order to make it react to the string with current and (number) then use this code:
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current.*?((d+))"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
This code will look for [anything] current [anything] ([number])
answered Jan 2 at 21:14
AndreasAndreas
17.1k41844
Same issue from @cybernetic.nomad solution - at least this is all i can think of as to why it doesn't work: "current" and "(1)" are in the same string: "if(string.tolower.current.accountemail(1)"
– max
Jan 2 at 22:13
@max I see no reason why it would fail on that string. Although I have not even tried the code since I posted it from my phone. I will try it on my computer soon. Give me a few minutes.
– Andreas
Jan 2 at 22:16
@max I see it now, without starting the computer. You said in questioncurrent and ([number]). Your string has noand. Is that not required? Do you meancurrentand([number])?
– Andreas
Jan 2 at 22:21
Yes! no and is required
– max
Jan 2 at 22:53
1
yep that does appear to work. Thanks man!
– max
Jan 3 at 16:32
|
show 1 more comment
If you really don't want to follow @Andreas suggestion (which is another way of saying "why didn't I think of that"), try:
=IFERROR(IF(FIND("current",A3)>0,CONCATENATE("within ", CHOOSE(VALUE(MID(A3,FIND("(",A3)+1,1)),5,10,15,20,25,30), " minutes"),),FALSE)
Caveat: it ignores the closing parenthesis, so this cell contains current and (1 would not return "FALSE"
answered Jan 2 at 21:07
cybernetic.nomadcybernetic.nomad
2,92621121
Holy cow that's a cool formula. That works darn well. It should capture everything I'm looking for. Thank ya man!
– max
Jan 2 at 21:14
Update on this - it looks like it works when current and (1) are in different strings but not if they're in the same string; example: if(string.tolower.current.accountemail(1)" this returns false. Trying to use the macro code now
– max
Jan 2 at 22:05
add a comment |
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%2f54012891%2fhow-to-find-a-cell-that-contains-parenthesis-around-a-number-e-g-1%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If VBA and UDF is ok then I advice you to use regex.
Open VBA editor (ALT +F11) and add a module.
Paste the following code and save the Excel workbook as macroactivated workbook (xlsm).
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*(current and (d+))"
' or if you want to match optional ()
'RE.Pattern = ".*(current and (?d+)?)"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = Matches.Item(0).submatches.Item(0)
End If
End Function
Use it as a formula like:
=Regex(A1)
It will return the part it's looking for current and [number]
The return of the code can of course be whatever you want.
But I don't understand the logic in your question that is why I return what it is looking for
I see now the logic.
This will return the output as you expect.
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current and ((d+))"
' or if you want to match optional ()
'RE.Pattern = ".*current and (?(d+))?"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
It multiplies the captured number with 5 to get the number of minutes.
If nothing is found then it returns False.
VBA and regex generally means the code in the worksheet is easier to maintain and easier to debug.
In order to make it react to the string with current and (number) then use this code:
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current.*?((d+))"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
This code will look for [anything] current [anything] ([number])
answered Jan 2 at 21:14
AndreasAndreas
17.1k41844
Same issue from @cybernetic.nomad solution - at least this is all i can think of as to why it doesn't work: "current" and "(1)" are in the same string: "if(string.tolower.current.accountemail(1)"
– max
Jan 2 at 22:13
@max I see no reason why it would fail on that string. Although I have not even tried the code since I posted it from my phone. I will try it on my computer soon. Give me a few minutes.
– Andreas
Jan 2 at 22:16
@max I see it now, without starting the computer. You said in questioncurrent and ([number]). Your string has noand. Is that not required? Do you meancurrentand([number])?
– Andreas
Jan 2 at 22:21
Yes! no and is required
– max
Jan 2 at 22:53
1
yep that does appear to work. Thanks man!
– max
Jan 3 at 16:32
|
show 1 more comment
If VBA and UDF is ok then I advice you to use regex.
Open VBA editor (ALT +F11) and add a module.
Paste the following code and save the Excel workbook as macroactivated workbook (xlsm).
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*(current and (d+))"
' or if you want to match optional ()
'RE.Pattern = ".*(current and (?d+)?)"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = Matches.Item(0).submatches.Item(0)
End If
End Function
Use it as a formula like:
=Regex(A1)
It will return the part it's looking for current and [number]
The return of the code can of course be whatever you want.
But I don't understand the logic in your question that is why I return what it is looking for
I see now the logic.
This will return the output as you expect.
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current and ((d+))"
' or if you want to match optional ()
'RE.Pattern = ".*current and (?(d+))?"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
It multiplies the captured number with 5 to get the number of minutes.
If nothing is found then it returns False.
VBA and regex generally means the code in the worksheet is easier to maintain and easier to debug.
In order to make it react to the string with current and (number) then use this code:
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current.*?((d+))"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
This code will look for [anything] current [anything] ([number])
answered Jan 2 at 21:14
AndreasAndreas
17.1k41844
Same issue from @cybernetic.nomad solution - at least this is all i can think of as to why it doesn't work: "current" and "(1)" are in the same string: "if(string.tolower.current.accountemail(1)"
– max
Jan 2 at 22:13
@max I see no reason why it would fail on that string. Although I have not even tried the code since I posted it from my phone. I will try it on my computer soon. Give me a few minutes.
– Andreas
Jan 2 at 22:16
@max I see it now, without starting the computer. You said in questioncurrent and ([number]). Your string has noand. Is that not required? Do you meancurrentand([number])?
– Andreas
Jan 2 at 22:21
Yes! no and is required
– max
Jan 2 at 22:53
1
yep that does appear to work. Thanks man!
– max
Jan 3 at 16:32
|
show 1 more comment
If VBA and UDF is ok then I advice you to use regex.
Open VBA editor (ALT +F11) and add a module.
Paste the following code and save the Excel workbook as macroactivated workbook (xlsm).
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*(current and (d+))"
' or if you want to match optional ()
'RE.Pattern = ".*(current and (?d+)?)"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = Matches.Item(0).submatches.Item(0)
End If
End Function
Use it as a formula like:
=Regex(A1)
It will return the part it's looking for current and [number]
The return of the code can of course be whatever you want.
But I don't understand the logic in your question that is why I return what it is looking for
I see now the logic.
This will return the output as you expect.
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current and ((d+))"
' or if you want to match optional ()
'RE.Pattern = ".*current and (?(d+))?"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
It multiplies the captured number with 5 to get the number of minutes.
If nothing is found then it returns False.
VBA and regex generally means the code in the worksheet is easier to maintain and easier to debug.
In order to make it react to the string with current and (number) then use this code:
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current.*?((d+))"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
This code will look for [anything] current [anything] ([number])
answered Jan 2 at 21:14
AndreasAndreas
17.1k41844
If VBA and UDF is ok then I advice you to use regex.
Open VBA editor (ALT +F11) and add a module.
Paste the following code and save the Excel workbook as macroactivated workbook (xlsm).
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*(current and (d+))"
' or if you want to match optional ()
'RE.Pattern = ".*(current and (?d+)?)"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = Matches.Item(0).submatches.Item(0)
End If
End Function
Use it as a formula like:
=Regex(A1)
It will return the part it's looking for current and [number]
The return of the code can of course be whatever you want.
But I don't understand the logic in your question that is why I return what it is looking for
I see now the logic.
This will return the output as you expect.
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current and ((d+))"
' or if you want to match optional ()
'RE.Pattern = ".*current and (?(d+))?"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
It multiplies the captured number with 5 to get the number of minutes.
If nothing is found then it returns False.
VBA and regex generally means the code in the worksheet is easier to maintain and easier to debug.
In order to make it react to the string with current and (number) then use this code:
Function Regex(Cell)
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")
RE.Pattern = ".*current.*?((d+))"
RE.Global = True
RE.IgnoreCase = True
Set Matches = RE.Execute(Cell)
If Matches.Count <> 0 Then
Regex = "within " & Matches.Item(0).submatches.Item(0)*5 & " minutes"
Else
Regex = "False"
End If
End Function
This code will look for [anything] current [anything] ([number])
answered Jan 2 at 21:14
AndreasAndreas
17.1k41844
edited Jan 2 at 22:27
answered Jan 2 at 21:14
AndreasAndreas
17.1k41844
answered Jan 2 at 21:14
AndreasAndreas
17.1k41844
answered Jan 2 at 21:14
AndreasAndreas
17.1k41844
17.1k41844
Same issue from @cybernetic.nomad solution - at least this is all i can think of as to why it doesn't work: "current" and "(1)" are in the same string: "if(string.tolower.current.accountemail(1)"
– max
Jan 2 at 22:13
@max I see no reason why it would fail on that string. Although I have not even tried the code since I posted it from my phone. I will try it on my computer soon. Give me a few minutes.
– Andreas
Jan 2 at 22:16
@max I see it now, without starting the computer. You said in questioncurrent and ([number]). Your string has noand. Is that not required? Do you meancurrentand([number])?
– Andreas
Jan 2 at 22:21
Yes! no and is required
– max
Jan 2 at 22:53
1
yep that does appear to work. Thanks man!
– max
Jan 3 at 16:32
|
show 1 more comment
Same issue from @cybernetic.nomad solution - at least this is all i can think of as to why it doesn't work: "current" and "(1)" are in the same string: "if(string.tolower.current.accountemail(1)"
– max
Jan 2 at 22:13
@max I see no reason why it would fail on that string. Although I have not even tried the code since I posted it from my phone. I will try it on my computer soon. Give me a few minutes.
– Andreas
Jan 2 at 22:16
@max I see it now, without starting the computer. You said in questioncurrent and ([number]). Your string has noand. Is that not required? Do you meancurrentand([number])?
– Andreas
Jan 2 at 22:21
Yes! no and is required
– max
Jan 2 at 22:53
1
yep that does appear to work. Thanks man!
– max
Jan 3 at 16:32
Same issue from @cybernetic.nomad solution - at least this is all i can think of as to why it doesn't work: "current" and "(1)" are in the same string: "if(string.tolower.current.accountemail(1)"
– max
Jan 2 at 22:13
Same issue from @cybernetic.nomad solution - at least this is all i can think of as to why it doesn't work: "current" and "(1)" are in the same string: "if(string.tolower.current.accountemail(1)"
– max
Jan 2 at 22:13
@max I see no reason why it would fail on that string. Although I have not even tried the code since I posted it from my phone. I will try it on my computer soon. Give me a few minutes.
– Andreas
Jan 2 at 22:16
@max I see no reason why it would fail on that string. Although I have not even tried the code since I posted it from my phone. I will try it on my computer soon. Give me a few minutes.
– Andreas
Jan 2 at 22:16
@max I see it now, without starting the computer. You said in question
current and ([number]). Your string has no and. Is that not required? Do you mean current and ([number])?– Andreas
Jan 2 at 22:21
@max I see it now, without starting the computer. You said in question
current and ([number]). Your string has no and. Is that not required? Do you mean current and ([number])?– Andreas
Jan 2 at 22:21
Yes! no and is required
– max
Jan 2 at 22:53
Yes! no and is required
– max
Jan 2 at 22:53
1
1
yep that does appear to work. Thanks man!
– max
Jan 3 at 16:32
yep that does appear to work. Thanks man!
– max
Jan 3 at 16:32
|
show 1 more comment
If you really don't want to follow @Andreas suggestion (which is another way of saying "why didn't I think of that"), try:
=IFERROR(IF(FIND("current",A3)>0,CONCATENATE("within ", CHOOSE(VALUE(MID(A3,FIND("(",A3)+1,1)),5,10,15,20,25,30), " minutes"),),FALSE)
Caveat: it ignores the closing parenthesis, so this cell contains current and (1 would not return "FALSE"
answered Jan 2 at 21:07
cybernetic.nomadcybernetic.nomad
2,92621121
Holy cow that's a cool formula. That works darn well. It should capture everything I'm looking for. Thank ya man!
– max
Jan 2 at 21:14
Update on this - it looks like it works when current and (1) are in different strings but not if they're in the same string; example: if(string.tolower.current.accountemail(1)" this returns false. Trying to use the macro code now
– max
Jan 2 at 22:05
add a comment |
If you really don't want to follow @Andreas suggestion (which is another way of saying "why didn't I think of that"), try:
=IFERROR(IF(FIND("current",A3)>0,CONCATENATE("within ", CHOOSE(VALUE(MID(A3,FIND("(",A3)+1,1)),5,10,15,20,25,30), " minutes"),),FALSE)
Caveat: it ignores the closing parenthesis, so this cell contains current and (1 would not return "FALSE"
answered Jan 2 at 21:07
cybernetic.nomadcybernetic.nomad
2,92621121
Holy cow that's a cool formula. That works darn well. It should capture everything I'm looking for. Thank ya man!
– max
Jan 2 at 21:14
Update on this - it looks like it works when current and (1) are in different strings but not if they're in the same string; example: if(string.tolower.current.accountemail(1)" this returns false. Trying to use the macro code now
– max
Jan 2 at 22:05
add a comment |
If you really don't want to follow @Andreas suggestion (which is another way of saying "why didn't I think of that"), try:
=IFERROR(IF(FIND("current",A3)>0,CONCATENATE("within ", CHOOSE(VALUE(MID(A3,FIND("(",A3)+1,1)),5,10,15,20,25,30), " minutes"),),FALSE)
Caveat: it ignores the closing parenthesis, so this cell contains current and (1 would not return "FALSE"
answered Jan 2 at 21:07
cybernetic.nomadcybernetic.nomad
2,92621121
If you really don't want to follow @Andreas suggestion (which is another way of saying "why didn't I think of that"), try:
=IFERROR(IF(FIND("current",A3)>0,CONCATENATE("within ", CHOOSE(VALUE(MID(A3,FIND("(",A3)+1,1)),5,10,15,20,25,30), " minutes"),),FALSE)
Caveat: it ignores the closing parenthesis, so this cell contains current and (1 would not return "FALSE"
answered Jan 2 at 21:07
cybernetic.nomadcybernetic.nomad
2,92621121
answered Jan 2 at 21:07
cybernetic.nomadcybernetic.nomad
2,92621121
answered Jan 2 at 21:07
cybernetic.nomadcybernetic.nomad
2,92621121
answered Jan 2 at 21:07
cybernetic.nomadcybernetic.nomad
2,92621121
2,92621121
Holy cow that's a cool formula. That works darn well. It should capture everything I'm looking for. Thank ya man!
– max
Jan 2 at 21:14
Update on this - it looks like it works when current and (1) are in different strings but not if they're in the same string; example: if(string.tolower.current.accountemail(1)" this returns false. Trying to use the macro code now
– max
Jan 2 at 22:05
add a comment |
Holy cow that's a cool formula. That works darn well. It should capture everything I'm looking for. Thank ya man!
– max
Jan 2 at 21:14
Update on this - it looks like it works when current and (1) are in different strings but not if they're in the same string; example: if(string.tolower.current.accountemail(1)" this returns false. Trying to use the macro code now
– max
Jan 2 at 22:05
Holy cow that's a cool formula. That works darn well. It should capture everything I'm looking for. Thank ya man!
– max
Jan 2 at 21:14
Holy cow that's a cool formula. That works darn well. It should capture everything I'm looking for. Thank ya man!
– max
Jan 2 at 21:14
Update on this - it looks like it works when current and (1) are in different strings but not if they're in the same string; example: if(string.tolower.current.accountemail(1)" this returns false. Trying to use the macro code now
– max
Jan 2 at 22:05
Update on this - it looks like it works when current and (1) are in different strings but not if they're in the same string; example: if(string.tolower.current.accountemail(1)" this returns false. Trying to use the macro code now
– max
Jan 2 at 22:05
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%2f54012891%2fhow-to-find-a-cell-that-contains-parenthesis-around-a-number-e-g-1%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
Can you please edit your question to give us sample data?
– cybernetic.nomad
Jan 2 at 20:49
I cannot reproduce your issue. Do you mean "current AND one of the numbers" or "current OR either of the numbers"? Your logic is not clear. Also,
(2)can be notation for-2, so depending on you cell contents it could be a number, not text. A data sample and/or a screenshot would help.– teylyn
Jan 2 at 20:51
Added a sample table screenshot
– max
Jan 2 at 21:04
Use regex and UDF.
– Andreas
Jan 2 at 21:05