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?

What you want to achieve cannot be done with a formula, because you need to iterate for every letter of the alphabet. You would need a function for that, unless you want to nest 26 Substitute functions. — teylyn, Apr 07 '15 at 11:18

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

0

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

answered Apr 07 '15 at 12:19

Gary's Student

19,266
6
25
39

That worked perfectly! Many thanks! (I don't have enough rep to upvote this answer, sorry!) – Rhiannon Apr 07 '15 at 15:14

Getting CHAR function to return multiple characters in Excel 2011

1 Answers1