Formula required for initials of names and combining initials with other text

Hi Guys,

I need some help here for making formulas

1) I want formula For initials Of a name :

Ilyas Abdul Rauf Shaikh = It should be like this - I A R S (with spaces)

Ilyas Rauf Shaikh = I R S

Ilyas R Shaikh = I R S

Ilyas Shaikh = I S

Ilyas S = I S

I Shaikh = I S

(i want a formula for all above cases)

2) Second one

Ilyas Shaikh
Code - 3FF21GT4123F1WQE

result should be like this First letter of Ilyas then Code and then last letter of shaikh

I3FF21GT4123F1WQEH (without Spaces )

Thanks in advance for help!

Hello & Welcome to the Forum,

For #1, try this UDF (courtesy norie)

Please Login or Register  to view this content.

Select Code >> Open your workbook

• Alt + F11 >> opens the Visual Basic Editor (VBE)
• Ctrl + R >> opens the Project Explorer (if not already open on left side of screen)
• Insert menu >> Module or Alt + I, M >> activates the Insert menu and inserts a Standard Module
• Paste code >> Ctrl + V (right side of screen)
• Alt + Q >> exits VBE and returns to Excel
• With First name in A1 >> B1 =FirstLetters(A1)

For #2 not so sure, might need some assistance here.

You only list one name, Ilyas Shaikh, but could it be any of the names above?
What if the name was Ilyas Abdul Rauf Shaikh?
What would you want then?

If it is only two names, a first and a last, try...

Name in A1 with code in B1
In C1 >> =LEFT(A1)&B1&UPPER(MID(A1,LEN(A1),1))

surly the second is just upper(left(a1))&code number&upper(right(a1))

Sir of above VBE codes i have come upto ur second last stage

I didnt understood your last step

Please explain

or do u have formula for it? like for 2nd one??

Please do reply.


I have found this formula but it does not create spaces between them

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

Hi Martin,

That would give the first letter of the last name!

Originally Posted by iDarkAngel

2) Second one

Ilyas Shaikh
Code - 3FF21GT4123F1WQE

result should be like this
First letter of Ilyas then Code and then last letter of shaikh

I3FF21GT4123F1WQEH (without Spaces)

This is the formula, but based off of my assumption its only first name and last name.

Originally Posted by jeffreybrown

Name in A1 with code in B1
In C1 >> =LEFT(A1)&B1&UPPER(MID(A1,LEN(A1),1))

Formula:

Please Login or Register  to view this content.

no, right gives last letter in cell =right("Ilyas Rauf Shaikh")= "h"

Sorry Martin, of course you are right. I was looking at it with blinders on...

@iDarkAngel,
Please attach a workbook with your exact requirements. This will help expedite the correct solution.

=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,LEFT(A1,1),IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,LEFT(A1,1)&" "&MID(A1,FIND(" ",A1)+1,1),LEFT(A1,1)&" "&MID(A1,FIND(" ",A1)+1,1)&" "&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1))) but it doesnt work for 4 names
so use
=IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=0,LEFT(A1,1),IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=1,LEFT(A1,1)&" "&MID(A1,FIND(" ",A1)+1,1),LEFT(A1,1)&" "&MID(A1,FIND(" ",A1)+1,1)&" "&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)))&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))=3," "&MID(A1,FIND("^",SUBSTITUTE(A1," ","^",3))+1,1),"")

@iDarkAngel,
Please do not wright in bold, big font, or underline like that again. We are neither deaf nor blind.

Please Login or Register  to view this content.

Nobody is asking you to do it or be good at it, it is all done.

See if this attachment works for you.

are you not reading the posts?

Please look at post #10. Formula provided by Martin

Change the cell reference to suit...

Try this workbook, it uses formulae as requested.
In C2

Please Login or Register  to view this content.

In D2

Please Login or Register  to view this content.

This will return up to 6 initials, expand,contract the formula to suit your needs.

My first and second formulas, forgot to post earlier, sorry if they duplicate any of the previous answers:

1)
=LEFT($A1) & MID($A1, FIND(" ", $A1), 2) & CHOOSE(LEN(A1)-LEN(SUBSTITUTE(A1, " ", "")), "", MID($A1, FIND(" ", SUBSTITUTE($A1, " ", "^", 1)), 2), MID($A1, FIND(" ", SUBSTITUTE($A1, " ", "^", 1)), 2) & MID($A1, FIND(" ", SUBSTITUTE(SUBSTITUTE($A1, " ", "^", 1), " ", "^", 1)), 2))


2) Assumes the name in A1, code in B1:

=UPPER(LEFT(A1) & B1 & RIGHT(A1))