Names to appear in Abbreviated form

**sushil10s** · 01-27-2012, 04:31 AM

Column A contains names like

Rahul Kumar Mishra
Rahul Kumar Vishwakarma
Sushil Kant Sharma
Ram Mohan Roy
Mohandas Karmachand Gandhi
Jawahar Lal Nehru

Want to know a formula in Column B to make them appear in abbreviated form like

RKM
RKV
SKS
RMR
MKG
JLN

Thanks

**teylyn** · 01-27-2012, 04:43 AM

Hello,

this works for cells with up to three names (two spaces)

=LEFT(A1,1)&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>0,MID(A1,FIND(" ",A1)+1,1),"")&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),"")

cheers,

**arlu1201** · 01-27-2012, 04:49 AM

Teylyn - just for my learning (i am still a noob at formulae)

Why do you use a ">1" in the 2nd if function in the above formula?
What if there were four names, would we have to put an additional if loop to track that?

**NickyC** · 01-27-2012, 04:58 AM

Hi

this function will work for any number of names - it takes the first letter from each word

Select alt+F11 and copy it to your VBA area

in the worksheet, type =Acronym_maker(A1) to return the first letters of the text in cell A1

Function Acronym_maker(rng As Range)
    Dim arrNos As Variant
    Dim n As Long 
    txt = ""
    arrNos = Split(rng, " ")
    For n = 0 To UBound(arrNos)
        txt = txt & Left(arrNos(n), 1)
    Next
    Acronym_maker = txt
End Function

**teylyn** · 01-27-2012, 05:02 AM

Originally Posted by arlu1201

Teylyn - just for my learning (i am still a noob at formulae)

Why do you use a ">1" in the 2nd if function in the above formula?
What if there were four names, would we have to put an additional if loop to track that?

As I said, the formula is for up to three names. It counts the spaces in A1. If the count is >1, then there are three names, and the third initial will be extracted.

If the cell can contain four names, the formula would need to be adjusted to accommodate that.

**sushil10s** · 01-27-2012, 09:27 AM

Originally Posted by teylyn

Hello,

this works for cells with up to three names (two spaces)

=LEFT(A1,1)&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>0,MID(A1,FIND(" ",A1)+1,1),"")&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),"")

cheers,

Hi

I got this:

=LEFT(B12,1)&MID(B12,FIND(" ",B12)+1,1)&MID(B12,FIND(" ",B12,FIND(" ",B12)+1)+1,1)

But I want to use Right( function as well.

Please suggest.

**teylyn** · 01-28-2012, 10:49 PM

But I want to use Right( function as well.

Why? It's not required to produce the result. Right() returns the rightmost character(s) of a string. If you want to pull initials from a name, Right() is not the right (!) function.