Brad Yundt
My feedback
-
115 votes24 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Brad Yundt
supported this idea
·
-
9 votes3 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
6 votes1 comment · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
133 votes11 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Brad Yundt
commented
Among other places, I evangelized this suggestion in Quora: https://www.quora.com/What-formula-removes-only-numbers-in-the-string-123ABC-ABC-3456BCDS-BCDS-256ERG-ERG/answer/Brad-Yundt
As of October 12, 2020 that thread has 5300 page views and 45 upvotes. That's over 40 times the number of page views and upvotes received by the "correct" answer to the question of how to remove all digits from a string (by using a god-awful nested SUBSTITUTE).
An error occurred while saving the comment Brad Yundt
commented
Sample VBA code implementing the suggestion shown below. A file with test cases is available (I tried to upload it, but failed).
Option Compare Text
Function Substitutes(text As String, OldText As Variant, NewText As Variant, Optional InstanceNum As Long = 0, Optional CaseSensitive As Boolean = False)
'Function works like SUBSTITUTE, except it can accept arrays for OldText and New Text, and InstanceNum may be negative _
If InstanceNum is 0, all matches to OldText elements are replaced with the corresponding element in NewText _
If NewText has only one value, then all matches to OldText elements are replaced with NewText _
If InstanceNum is a positive integer, that instance of a match (counting from beginning of string) is replaced with NewText _
If InstanceNum is a negative integer, that instance of a match (counting from the end of string) is replaced with NewTextDim i As Long, j As Long, Matches As Long, n As Long, nOldText As Long, nNewText As Long
Dim v As Variant, Texts As Variant
Dim b As Boolean, bTest As Boolean
Dim TextCompare As Integer
Dim s As String, s2 As StringIf VarType(OldText) = 8 Then
nOldText = 1
Else
For Each v In OldText
nOldText = nOldText + 1
Next
End IfIf VarType(NewText) = 8 Then
nNewText = 1
Else
For Each v In NewText
nNewText = nNewText + 1
Next
End IfIf (nNewText > 1) And (nOldText > nNewText) Then
Substitutes = CVErr(xlErrValue)
Exit Function
End IfReDim Texts(1 To nOldText, 1 To 2)
i = 0
If nOldText = 1 Then
Texts(1, 1) = OldText
Else
For Each v In OldText
i = i + 1
Texts(i, 1) = v
Next
End IfIf nNewText = 1 Then
For i = 1 To nOldText
Texts(i, 2) = NewText
Next
Else
i = 0
For Each v In NewText
i = i + 1
If i > nOldText Then Exit For
Texts(i, 2) = v
Next
End Ifn = Len(text)
s2 = text
If InstanceNum >= 0 Then
For j = 1 To n
For i = 1 To nOldText
bTest = IIf(CaseSensitive, InStr(j, s2, Texts(i, 1), vbBinaryCompare) = j, Mid(s2, j) Like (Texts(i, 1) & "*"))
If bTest Then
Matches = Matches + 1
If Matches = InstanceNum Then
b = True
If j > 1 Then s = Left(s2, j - 1)
s2 = s & Texts(i, 2) & Mid(s2, j + Len(Texts(i, 1)))
Exit For
ElseIf InstanceNum = 0 Then
If j > 1 Then s = Left(s2, j - 1)
s2 = s & Texts(i, 2) & Mid(s2, j + Len(Texts(i, 1)))
If Len(Texts(i, 2)) > 0 Then j = j + Len(Texts(i, 2)) - 1
n = n - Len(Texts(i, 1)) + Len(Texts(i, 2))
End If
End If
Next
If b = True Then Exit For
Next
Else
For j = n To 1 Step -1
For i = 1 To nOldText
bTest = IIf(CaseSensitive, InStr(j, s2, Texts(i, 1), vbBinaryCompare) = j, Mid(s2, j) Like (Texts(i, 1) & "*"))
If bTest Then
Matches = Matches - 1
If Matches = InstanceNum Then
b = True
If j > 1 Then s = Left(s2, j - 1)
s2 = s & Texts(i, 2) & Mid(s2, j + Len(Texts(i, 1)))
Exit For
End If
End If
Next
If b = True Then Exit For
Next
End IfSubstitutes = s2
End FunctionAn error occurred while saving the comment Brad Yundt
commented
Another good feature to add to such a function would be an optional Boolean variable for case sensitive matching.
Wildcard matching ought to be part of the SUBSTITUTES function, so I added it to my sample code (but only for case insensitive matching).
Brad Yundt
shared this idea
·
-
150 votesBrad Yundt
supported this idea
·
-
52 votes1 comment · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
21 votes1 comment · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
1,530 votes222 comments · Excel for Windows (Desktop Application) » Tables, Sorting and Filtering · Flag idea as inappropriate… · Admin →
Thanks for logging this great suggestion, Zack, and to others for voting it up. We’ll prioritize this according to the number of votes, so if there’s more interest, please make sure to register your vote!
Thanks
Ashvini Sharma
Lead Program Manager
ExcelBrad Yundt
supported this idea
·
-
627 votes43 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →
Thank you for taking your time to suggest and vote for allowing interaction with Excel while in Power Query Editor window. We do think that this suggestion has merit but we don’t think that we’ll be able to devote time to it in the near future. We’ll continue tracking votes for it.
Guy Hunkin
Excel TeamBrad Yundt
supported this idea
·
-
4,130 votes
Thanks for supporting this suggestion with your votes. It’s unlikely that we’ll be able to bring PowerPivot to Excel for Mac, because it relies on features of the operating system that don’t exist on Mac OS. We’ll keep tracking votes, since we know that the scenarios made possible by PowerPivot are important, even if the solution will need to be something else.
Brad Yundt
supported this idea
·
-
291 votes30 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
14 votes3 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
4 votes4 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Brad Yundt
commented
I posted a suggestion to create a SUBSTITUTES function with the possibility of entering arrays/ranges for both old_text and new_text at https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/39470683-substitutes-function-supporting-arrays-of-find-a This idea already has 35 votes, so is already on Microsoft's radar screen. If you add your votes to that thread, what you were requesting here is more likely to happen.
Brad Yundt
supported this idea
·
-
20 votes5 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Thanks for the suggestion Rolands! I’d be curious to see, in comments, what other constants people are interested in. I’ll check back on this later, and of course the more votes it has the more likely it is to get into the product – so please keep the votes coming if you like the idea!
Best,
John [MS XL]An error occurred while saving the comment Brad Yundt
commented
Even though I ought to be a big user of it, I never use the CONVERT function because I can never remember which types of conversions are possible and how to specify the to units and from units.
Making a function for scientific/mathematical constants will need to overcome this problem because the function won't get used if you need to look up the syntax each time you try to use it. One way is to restrict the number of possible units for each constant. If the only choices were SI, MKS, customary metric, customary English or dimensionless, users might actually be able to remember them.
I also suggest making a single function with two parameters where the user has to specify the type of constant sought by name as well as the desired units.
To make it even more user friendly, let us choose both parameters using dropdowns in the formula wizard. Everybody from high school students through PhD researchers would find the scientific constant function discoverable if you did that.
Brad Yundt
supported this idea
·
-
130 votes7 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Thanks to RH for posting this suggestion. If avoiding #REF! errors is important to you also, please add your vote to support RH’s suggestion. We will prioritize accordingly.
Thanks,
Steve (MS Excel)Brad Yundt
supported this idea
·
-
76 votes20 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
165 votes42 comments · Excel for Windows (Desktop Application) » Other · Flag idea as inappropriate… · Admin →
This is a great suggestion, but we’re marking it “Not at this time”, because there are many dialogs in Excel and we don’t have a plan to address all of them at one time. If there are specific dialogs you want improved, please vote for specific suggestions about those, or leave comments here. We’ll keep tracking the votes on this one.
There is some good news. We’re working on making the Conditional Formatting dialogs resizable in Excel for Windows.
Here’s more about that –
https://excel.uservoice.com/forums/304921/suggestions/16092793Steve [Microsoft Excel]
An error occurred while saving the comment Brad Yundt
commented
I am publicizing this Excel UserVoice thread in Quora. As of July 25, 2020 there are 245 hits at https://www.quora.com/Despite-massive-changes-to-Excel-over-the-years-why-is-the-goto-dialog-still-not-resizable-Add-your-vote/answer/Brad-Yundt?__nsrc__=4&__snid3__=5953336707
Brad Yundt
supported this idea
·
-
289 votes43 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
284 votes33 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
52 votes5 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
When developing a named formula, LAMBDA or LET, there needs to be a better experience for debugging. Improved error messages would help. Ideally, we would be able to enter some test values and evaluate the formula in steps. Right now, the LAMBDA has to be perfect before it is accepted, so it can be difficult to see where the problem lies.