Feedback by UserVoice

Brad Yundt

My feedback

  1. 3,237 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    721 comments  ·  Excel for Mac » PivotTables and Power Pivot  ·  Flag idea as inappropriate…  ·  Admin →
    Brad Yundt supported this idea  · 
  2. 112 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  3. 11 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  4. 5 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    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  · 
  5. 16 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    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  · 
  6. 85 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    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 NewText

    Dim 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 String

    If VarType(OldText) = 8 Then
    nOldText = 1
    Else
    For Each v In OldText
    nOldText = nOldText + 1
    Next
    End If

    If VarType(NewText) = 8 Then
    nNewText = 1
    Else
    For Each v In NewText
    nNewText = nNewText + 1
    Next
    End If

    If (nNewText > 1) And (nOldText > nNewText) Then
    Substitutes = CVErr(xlErrValue)
    Exit Function
    End If

    ReDim 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 If

    If 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 If

    n = 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 If

    Substitutes = s2
    End Function

    An 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  · 
  7. 113 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  8. 69 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  9. 150 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    An error occurred while saving the comment
    Brad Yundt commented  · 
    Brad Yundt supported this idea  · 
  10. 218 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  11. 185 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  12. 46 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  13. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  14. 73 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  15. 5 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  16. 3 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  17. 11 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  18. 10 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
  19. 150 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    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  · 
  20. 32 votes
    Sign in
    (thinking…)
    Sign in with: Facebook Google
    Signed in as (Sign out)

    We’ll send you updates on this idea

    Brad Yundt supported this idea  · 
← Previous 1 3

Feedback and Knowledge Base