Brad Yundt
My feedback
-
3,237 votes
Thanks for all the votes and comments on this suggestion. We know this is important and it’s a big undertaking. Please continue to show your support for this.
Steve K [MS Excel]
Brad Yundt
supported this idea
·
-
112 votes22 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
11 votes3 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
5 votes4 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
·
-
16 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
·
-
85 votes7 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
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
·
-
113 votes6 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
·
-
69 votes19 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
150 votes34 comments · Excel for Windows (Desktop Application) » Other · Flag idea as inappropriate… · Admin →
An error occurred while saving the comment Brad Yundt
commented
I am publicizing this Excel UserVoice thread in Quora. As of December 3, 2019 there are 80 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
·
-
218 votes34 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
185 votes29 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
46 votes5 comments · Excel for Windows (Desktop Application) » Formatting · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
3 votes0 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
73 votes3 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →
Thanks for your suggestion!
I encourage you to continue voting for this idea. It helps us to prioritize our investments.
Thanks,
Guy Hunkin
Excel Program ManagerBrad Yundt
supported this idea
·
-
5 votes0 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
3 votes0 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
11 votes2 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
10 votes6 comments · Excel for Windows (Desktop Application) » Editing · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
-
150 votes16 comments · Excel for Windows (Desktop Application) » Formulas and Functions · Flag idea as inappropriate… · Admin →
Good suggestion Frederic – thanks for taking the time to post it. We’ll take a look at the F4 loop and including other functionality like this as soon as we can. We’ll be spending more time on the things with more votes – so folks that really want to see this should keep voting it up.
Best,
John [MS XL]Brad Yundt
supported this idea
·
-
32 votes7 comments · Excel for Windows (Desktop Application) » Data Import · Flag idea as inappropriate… · Admin →Brad Yundt
supported this idea
·
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.