Getting CHAR function to return multiple characters in Excel 2011

Question

I'm using Office 2011 on Mac.

I'm trying to write a formula to strip lower-case characters (and ideally spaces as well, but that's less important) from text in a cell. The purpose is to convert names to initials; thus "Jane" would become "J", "DeLacy" would become "DL", "Mary-Anne" would become "M-A", and "Anna Elise" would become "AE".

So far I have this (assuming the name is in B2), for stripping lower-case characters:

=SUBSTITUTE(B2,CHAR(ROW(97:122)),"")

However, this only removes "a" (so "Jane" becomes "Jne"). The problem seems to be that the CHAR function is returning only the first character rather than the range specified:

=CHAR(ROW(97:122))

returns "a".

So does

=CHAR(ROW(INDIRECT("97:122")))

How do I get it to return the whole range so that all the lower-case characters are removed?

score 0 · Accepted Answer · answered Apr 07 '15 at 12:19

Consider using a small User Defined Function (UDF):

Public Function LowerKiller(sIn As String) As String
    Dim L As Long, LL As Long
    Dim sCh As String, temp As String
    For L = 1 To Len(sIn)
        sCh = Mid(sIn, L, 1)
        If sCh Like "[a-z]" Or sCh = " " Then
        Else
            temp = temp & sCh
        End If
    Next L
    LowerKiller = temp
End Function

Getting CHAR function to return multiple characters in Excel 2011

1 Answers1