CODEWITHSUNDEEP
excel
Marshall Telaumbanua
Marshall Telaumbanua

Reputation: 25

formula unique character from first char in cell text

have any idea for create unique character from cell in excel?

example : i have string in cell A1 = "lorem lipsum bla bla"

and A2 with formula generate char from A1 like "LLBB"; just get first char

Upvotes: 0

Views: 73

Answers (2)

Scott Craner
Scott Craner

Reputation: 152525

with Office 365 Excel you can use CONCAT() in an array formula:

=UPPER(CONCAT(LEFT(TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),(ROW(1:20)-1)*99+1,99)))))

Being an array formula it must be confirmed with Ctrl+Shift+Enter instead of Enter when exiting edit mode. If done correctly Excel will put {} around the formula.

Replace the 20 with the max number of words it can be.

enter image description here

Upvotes: 2

Gary's Student
Gary's Student

Reputation: 96763

Try this small User Defined Function:

Public Function fLetter(rng As Range) As String
    Dim a
    For Each a In Split(rng.Value, " ")
        fLetter = fLetter & UCase(Left(a, 1))
    Next a
End Function

enter image description here

Upvotes: 1

Related Questions