How to convert to percentage

Question

Please help me how to convert this cell on Excel from number to percent

the account: Banks - Global 0.308123555052582 --Internet Content & Information 0.307825851134657 --Consumer Electronics 0.383936697049749

to become

the account: Banks - Global 30.81% --Internet Content & Information 30.78% --Consumer Electronics 38.39%

Thank you.

Answer 1

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

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.

Answer 2

@BigBen has a nice sophisticated solution. I'd probably do a 'dirty workaround', something like:

• In column A: find and replace a space followed by 0 by some divider ('|0')
• insert helper column B
• text to columns on column A, split on the divider put in place in step 1
• format the cells in column B to be %
• concatenate back together if needed.