[Solved] How to convert to percentage [closed]

by


Perhaps the following VBA solution using Regular Expressions.

Function FixMyPercentages(target As String) As String
    Dim output As String
    output = target
    With New RegExp
        .Global = False
        .MultiLine = True
        .IgnoreCase = False
        .pattern = "\s\d+\.\d+$|^\d+\.\d+\s|\s\d+\.\d+\s"

        Dim myMatch As Object, myMatches As Object

        Do While .test(output)
            Set myMatches = .Execute(output)
            For Each myMatch In myMatches
                output = .Replace(output, " " & Format$(CDbl(myMatch.Value), "0.00%") & " ")
            Next myMatch
        Loop
    End With
    FixMyPercentages = Trim(output)
End Function

enter image description here

To implement this:

  1. Alt + F11 to open the VB editor (or Developer > Visual Basic.)
  2. Then Insert > Module
  3. Paste the below code.
  4. Add a reference to Microsoft VBScript Regular Expressions 5.5 under Tools > References
  5. And then use it as a formula in your worksheet.

enter image description here

8

solved How to convert to percentage [closed]