Often when you are processing customer records or doing mail merge, it might be useful to get initials from a given name, like JFK for John F Kennedy.
You can do this using simple text formulas (left(), mid(), find()) combined with if(). Here is how:
Assuming cell B3 has the full name, then this is the formula you can use to get the Initials:
=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)))
As you can see, I have used different logic to find initials, based on the number of spaces in the name.
For the sake of simplicity I have limited the formula for names with three, two and one part only (ie first name, middle name and last name), for some reason if the name has more than 3 parts, then this formula would result in initials for the first three chunks of the name. See the example on google docs.
More on names and text formulas: Find word count using excel formulas, 15 excel formulas for everyone, Generate tag clouds using VBA.
90 Responses to “Initials from Names using Excel Formulas”
Here's my formula I've used in the past.
=LEFT(B3,1)&IF(ISERROR(FIND(" ",B3,1)),"",MID(B3,FIND(" ",B3,1)+1,1))&IF(ISERROR(FIND(" ",B3,FIND(" ",B3,1)+1)),"",MID(B3,FIND(" ",B3,FIND(" ",B3,1)+1)+1,1))
I know your formula works but I'm not sure how. I really trying to get it but a bit I'm lost. Why remove the spaces with substitute?
=LEFT(B3,1)&(MID(B3,FIND(" ",B3,1)+1,1))&MID(B3,FIND(" ",B3,FIND(" ",B3)+1)+1,1)
=LEFT(B2,1)&MID(B2,FIND(" ",B2,1),2)
=LEFT(B2)&MID(B2,FIND(" ",B2),2)
Hi I have used your formula. Only problem is the names are last name then first name, is there a way to turn them around in the code.
thank you.
Not sure if you're asking to swap the names or the initials.
Try this:
=IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=0,LEFT(B2,1),IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=1,MID(B2,FIND(" ",B2)+1,1)&LEFT(B2,1),IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=2,MID(B2,FIND(" ",B2)+1,1)&MID(B2,FIND(" ",B2,FIND(" ",B2)+1)+1,1)&LEFT(B2,1),IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=3,MID(B2,SEARCH(CHAR(127),SUBSTITUTE(B2," ",CHAR(127),2))+1,1)&MID(B2,SEARCH(CHAR(127),SUBSTITUTE(B2," ",CHAR(127),3))+1,1)&LEFT(B2,1),""))))
@Branden... both formulas should work the same way.
I am using substitute to findout how many spaces are in the customer name and then using that to find initials.
if no spaces.. initials is left(name,1)
if one space .. initials is left(name,1)&mid(name,firstspace+1,1)
if more than one spaces ... left(name,1)&mid(name, firstspace+1,1)&mid(name,secondspace+1,1)
the names I want to get the initials from are first name last and last name first. Can they be swapped around so the initials are first name fist initial.
Thanks, your explanation makes more sense to me.
I see that you compare the "original text" to the "no spaces" text to see how many spaces. So "Your Name" = 9 compared to "YourName" =8, 9-8=1 space. Then the if statement says do this for 0, do that for 1 and do another for 3.
It seems so simple to me now, thanks again.
Chandoo, I do appreciate a lot your tips in here, but...
Non-English versions of MS Excel translates function names into their version languages. Therefore, when I read your blog and try to practice, sometimes I don't find the equivalente function in my version of Excel. I know they exist, I just can't find them. So, even being clear in your explanations, I'd like to ask you to ALWAYS put a simple worksheet example available to download, as you do sometimes... Then I will be able to download it, open in my Portuguese version of Excel and finally see the function name in my own language...
Thanks!
@Bruno@Brazil:
Maybe this link will help:
http://cherbe.free.fr/traduc_fonctions_xl97.html
You could easily copy the table on the website to an excel file, delete everything but the English and the Portuguese column and use a VLOOKUP (PROCV) to find the Portuguese name of the function you are looking for.
@ Robert:
Thanks dude! It surely will help a lot! Maybe there are still some functions in newer versions of Excel I still have to find, but this website is indeed a great help.
Maybe I'll practice what it was covered once here and create a web query of this page!
Thanks!
@Bruno@Brazil:
I just remembered that Microsoft is delivering an excel file with every version of Office / Excel that includes a translation table for worksheet functions and vba functions from English to the installed language. In German the name of the file is vbaliste.xls, but - to be honest - I don't have a clue how the name might be in Portuguese. It is stored somewhere in the folder Program Files / Microsoft Office. Maybe you could do a search for VBA*.XLS and check what you are finding?
Otherwise: A german guy on a German website claims that the following link would provide such a translation table as well:
http://www.mondial.com.br
I can't tell you whether this is true or not, I don't speak a word Portuguese.
Desculpe!
@ Robert:
Once again, you're right! There is a VBALIST.XLS, which is a translation table from English to Portuguese of all commands and functions!
Thanks again !!!
@Bruno@Brazil
You are welcome.
This is somehow typically Microsoft. They provide a lot of useful features and other stuff but they don't tell us how to get there. See the vbalist.xls or the camera object, for example.
Here are a couple counter-examples:
Ken Griffey, Jr. (KGJ)
Thurston Howell III (THI)
PS. The fields and labels in the comment form are misaligned.
PPS. Daily Dose of Excel covered this recently with a lot of complicated approaches such as launching Word to use its UserName to UserInitials conversion, reading the user's initials from the windows logon info in the registry, some worksheet formulas, and a VBA function. The discussion there degenerated into a discussion of ways to get the user's "real" name in order to parse it for initials.
http://www.dailydoseofexcel.com/archives/2008/08/28/user-initials-in-excel/
@Jon... your counter examples are good. I have written about a simple way to get the initials from the names, in order to avoid special cases like this, either the formula becomes longer or we have to use some VBA, which I try to avoid. 🙂
Thanks for the daily dose link.
Also, can you post a screenshot of the misaligned fields, I am able to see it fine on my mac (both firefox and safari), but I am sure windows rendering must have been garbled, yesterday I tried fixing it.. but seems like still broken. thanks in advance.. 🙂
The screen shot is at
http://peltiertech.com/Sample/ChandooCommentForm.png
It's the same in FireFox 2 and IE6.
@Jon... I tried to fix this issues.. can you check it and tell me if it is looking ok? 🙂
Still looks weird, but now IE6 and FF2 are different.
FF2
http://peltiertech.com/Sample/ChandooCommentForm2ff.png
IE6
http://peltiertech.com/Sample/ChandooCommentForm2ie.png
It seems to be putting the field labels after the fields. I'd suggest looking over your php and css for comments, and comparing them to ones in free WordPress themes that you like.
Hi
I am stuck with a problem, your help will be great.
I have a data collection model, where I wish the vlookup function to look up in a particular file out of say 10 files, based on the value in previous cell.
Ex.
If A2 contains "AB", then the formula in A3 should lookup for value of A1 in ab.xls; if A2 contains "PQ", A3 should lookup for value of A1 in pq.xls.
I tired defining the lookup range as a name (AB and PQ in above case) and successfully used the names in vlookup function. Problem is when i ask excel to look for filename based on value of A2, it fails.
=IF($B7="","",IFERROR(VLOOKUP($B7,AB,4,0),"")) - this works (AB is name, hardcoded in the function)
=IF($B7="","",IFERROR(VLOOKUP($B7,A2,4,0),"")) - this fails (I am asking excel to look in A2 to get the name and look in appropriate file)
Tried using =A2... etc, did not work. Looking for a non VBA solutin, if possible.Help !! Thanks !!.
Sudhir -
Look at the VLookup syntax. The second argument is a range. If AB is a defined name, it may well be a range. A2 is a one-cell range, and Excel tries using it in VLookup, which fails. You need to use some kind of formula that indicates range. Something like this, which is not tested:
=IF($B7=”",”",IFERROR(VLOOKUP($B7,INDIRECT(ADDRESS(1,1,1,true,A2):ADDRESS(100,10,1,true)),4,0),”"))
This formula uses ADDRESS to provide a string address, in the first instance to cell A1, in the second to J100, so it is the range A1:J100.
Cell A2 has to contain the workbook name and sheet name in this format:
[WorkBook1.xls]SheetName
@Sudhir,
in addition to Jon's comment: please keep in mind that INDIRECT is only working as long as the workbook in its reference is open, otherwise it will return a #REF error.
Hi Jon/Robert
Tks for chipping in. I tried the options. First ofcourse, the indirect comes with the limitation which is undesirable. (I don't want the user to open all the 10 odd files before accessing the summary workbook).
Second the Address range options keeps failing, and i was unable to replicate the range.
Again, I was able to replicate using name - the problem is it is not dynamic, and gets hardcoded. Any other ideas?
@Sudhir,
I am sorry, but I don't know a way of creating a forumla dynamically referring to closed workbooks (according to the filename stored in a cell) WITHOUT VBA.
Maybe Frank Kabel's post on Daily Dose Of Excel will help:
http://www.dailydoseofexcel.com/archives/2004/12/01/indirect-and-closed-workbooks/
Tks Robert. You stressed on the 'Without VBA' .... so there is a solution with VBA ? It is just that I have not ventured into VBA and find the mumbo jumbo alittle bewildering ... know any site where I can pick VBA (for excel) basics ?
@Sudhir,
first of all sorry for "stressing" by using capital letters. I just didn't know how to highlight it in another, more polite way, since there is no way to use bold or italic when writing a comment...
I hope you don't mind.
If you don't mind using an add-in, the MOREFUNC-add-in, mentioned in the DDOE-link above is offering an INDIRECT-formula that works with closed workbooks. I just noticed that the link on DDOE doesn't work anymore. Here is a link that is still working and where you can download that free add-in:
http://www.download.com/Morefunc/3000-2077_4-10423159.html
If you don't want to use an add-in (which would have to be installed on all computers of the users of your workbook), take a look at the code provided by Harlan Grove (#3 on DDOE-post, the link is still working).
If you want to learn more about using the power of VBA in general, I highly recommend one of John Walkenbach’s books:
http://www.amazon.com/Excel-2003-Power-Programming-VBA/dp/0764540726/ref=pd_bbs_sr_2?ie=UTF8&s=books&qid=1222025042&sr=8-2
or
http://www.amazon.com/Excel-Programming-Dummies-Computer-Tech/dp/0764574124/ref=pd_bbs_11?ie=UTF8&s=books&qid=1222025042&sr=8-11
@Sudhir... That is a great discussion you have started there...
I have once built a workbook that would consolidate information from 20 different workbooks (each having one plan document in std. format) and prepare a report on the final workbook.
The limitation of refences to closed workbooks can be overcome by using full physical path of the document instead of just the name in the file name field... for eg:
='c:\docs\xl_files\[ab.xls]sheet1'!a1should refer to ab.xls even when it is closed. (takes more time though)I guess this should help you with closed books as long as the data is in A1 in all the workbooks, (actually if you know where it is upfront, that should do)
@Jon, Robert, thanks for the awesome links and discussion.
@Chandoo,
I might have misunderstood Sudhir's request, but I think the crucial point is the fact that the filenames are variable within his summary workbook and probably dynamically created in cells.
I could imagine the challenge is something like this: The user should be able to select a date (reporting week or month) in the summary workbook. The summary workbook creates the corresponding filenames by using formulae, e.g. sales_west_february_08.xls, sales_south_february_08.xls, etc. and retrieves the data by VLOOKUPs or other formulae referring to theses files .
If you want to have dynamically created filenames in formulae, you could either use the INDIRECT-function (see Jon’s comment, but all workbooks would have to be open) or you have to use VBA.
This is based on my humble state of knowledge. If anyone has a solution without VBA, please let me know.
@Chandu.... Tks to you for providing this podium.
Let me elucidate the requirement precisely. I have say 10 files representing each work site (naming is simple - 2char site code - LN.xls for London, etc). each xl reports the work effort in different sites. the summary sheet clubs together all this data. The problem - a person of one site can support multiple sites and hence, the name will appear in more than one xls. However, if he applies leave, it will be accounted only in his base site XL. So, I ask xl to look for column A2 (which describes the base sitecode for the name in A1) and look for the leave entry in that specific XLS which is basically .xls - ab.xls. So if A2 is LN, the vlookup in A3 must look in spcified cell in LN.xls. Because there is Vlookup involved here, the lookup is going bust. Currently, i have hardcoded the vlookup with the site code (file name) entered by me. This will fail, if the specific person is moved from one site to another, becuase then it will not register the leave !!. I am trying to see, if I can avoid hardcoding. Looks difficult though ( and excitingly challenging !).
@Robert ... the mistake is mine. As Sudhir mentioned in the next comment.
@Sudhir ... your explanation makes it easy to visualize your problem. I have few questions though.
Is this how your summary sheet table looks like?
A B C D (columns)
Jon Robert Sudhir Chandoo
ab.xls bc.xls cd.xls df.xls
and now A3 has a vlookup formula.
Now my question is...
1. do you always know where Jon's data is on ab.xls? Is it in one cell (like a1)?
Can you tell me why you are using lookup formula, if you need just one cell that has the data? Am I missing something.. please enlighten us..
@Chandu... it's interesting model actually.
A - all the names of people at all sites, one row per person.
B - base site code
C - leave (which is the crux of problem - to pick from base site code)
D onwards, (say)5 cols each for sub projects in site ab, then cd, ef etc. (picks from ??.xls, by lookup of name. The names are on LOV from validation). This way, when I pick say name sudhir from LOV in col A, cols d thru (say)Z show me his work done in all sub projects across 10 sites. and a simple col at the end shows burn analysis (and a caution if sudhir has been sleeping at work 🙂 )
I have ensured by model that all reporting sheets (ab.xls, cd.xls etc) are exactly copies and data model sheets are protected from change.
At the end of reporting period, all the ten sheets are dumped in the specified folder, open the tracker sheet, run refresh which picks up effort by looking up the names - copy and paste special into a 'frozen status'. Any suggestions to betterment ? Data quantum is fairly large - about 100 sub projects, and a large team. I wish i were able to tackle that leave hardcoding.
@Sudhir,
here is one possible solution for your request using vba:
As you said, let’s assume in your summary workbook you have the names of all people in column A, the base site code in column B (e.g “LN” in row 1 and “NY” in row 2) and you want to create the following VLOOKUPs in column C:
Formula in C1: VLOOKUP (A1, [LN.xls]Sheet1!$A$1:$B$10,2,0)
Formula in C2: VLOOKUP (A2, [NY.xls]Sheet1!$A$1:$B$10,2,0)
etc.
How about writing a vba procedure that simply creates these formulas based on the values in column B and pastes it into column C?
Go to the VBA editor, insert a new module and copy the following code into this module:
Sub Create_My_Vlookup_Formulas()
Dim i As Integer
For i = 1 To 20
Range ("C1").Offset(i - 1, 0).Formula = _
"=VLOOKUP(" & Range("A1").Offset(i - 1, 0).Address & ", '" & ThisWorkbook.Path & "\[" & Range("B1").Offset(i - 1, 0).Value & ".xls]Sheet1'!$A$1:$B$10,2,0)"
Next i
End Sub
Whenever you run this procedure, the formulas in the first 20 rows of column C will be created according to the filename defined by the base site codes in column B. All input files have to be stored in the same folder as your summary workbook.
You can call this procedure manually whenever you need it or you call it from a workbook_open or a worksheet_calculate function.
The code is everything else but elegant and has lots of potential for improvements (hardcoding the number of used rows, hardcoding the references A1, B1, C1, hardcoding the name of the sheets and the cell reference of the lookup matrix, etc.).
I know I shouldn’t do hardcoding at all (and I usually do not). But in this case I tried to keep the code as simple as possible in order to help you to get the basic idea.
Let me know what you think.
@Robert...
it looks like the problem is with getting the path of the file? We use cell("filename") formula for that ... ?
@sudhir... unfortunately I have no access to Excel for the next 3-4 days, so I cant really test / replicate your condition. But here is one thing I have in mind ...
Is it possible to have a range name created in each of the 20 sheets (range name would be same across) and then refer to the range name along with full file path?
@Chandoo:
you can use CELL (and INFO) to get information about the filename and the path as a string. And of course you can concatenate a string that looks like a reference to a cell or a range in another worksheet of another workbook even in another folder.
The problem: VLOOKUP (and every other Excel-formula) does not accept a string as a reference. INDIRECT is the function to convert strings into references so they can be used in formulas. But as said above, INDIRECT only works if the other workbook is open.
That is why – in my humble opinion – there is no way to do this without VBA.
@Sudhir:
Maybe you want to give my idea with VBA a try (see my recent comment above). Please do not hesitate to come back, if it doesn’t work.
@Chandoo:
As Robert mentions, we have tried your suggestions already...each comes with its own limitation. Looks like VBA is the only way out.
@Robert - thanks for the tips on VBA. This will be a first for me in VBA... so prob I will go ahead with hardcoding for now, and take your VBA approach from academic point for initiating myself into VBA.
Thanks all for the dynamic conversation all along !!
Sudhir
[...] spelling mistakes in your data Splitting text using excel formulas Generating initials from names using excel Adding a range of cells using [...]
[...] Extracting Initials from Names using Excel Formulas 33 Comments [September 02] [...]
[...] | user-saved public links | iLinkShare 3 votesInitials from Names using Excel Formulas>> saved by MissTilaTequila 2 days ago1 votesSomewhat Less Del.icio.us>> saved by crazykarl7 4 days [...]
@ Chandoo
Cud u help me to reverse the any text with "n" characters ,including blank, using excel formula and not thru VBA.
e.g. : text : "abc defgh i"
Require result : "i hgfed cba"
@Ketan
To my knowl, it cannot be done. But assuming your entry field is small (say four to six chars) and for one field only (not a database or column of values), here's a small workaround to achieve it. Tried successfully for 6 chars.
Entry : A1, Output C1 will have (=A8&A7&A6&A5&A4&A3)
Cells B3 thru B8 have numbers in serial 1 thru 6
Cell A3=MID($A$1,B3,1)
Cell A4=MID($A$1,B4,1)
Cell A5=MID($A$1,B5,1)
Cell A6=MID($A$1,B6,1)
Cell A7=MID($A$1,B7,1)
Cell A8=MID($A$1,B8,1). Result : In : CREDIT Out : TIDERC
You may place this module in a work sheet and place only the input and output onto the main sheet.
@Ketan: Thanks for asking this question. Unfortunately I dont know how to reverse a string in excel without using VBA or assuming the string's length (see Sudhir's comments above)
Can anybody suggest a formula hack for this... as usual e-donuts for you if you get it right...
Ketan, Chandoo,
the best way to reverse texts is using the VBA-function StrReverse. But it is possible to do this with formulas as well. Too complicated to explain in a comment here, I guess.
That's why I uploaded a file with the 3 solutions (3 worksheets):
1. Reverse texts using formulas (including circular references and iterations)
2. Reverse texts using VBA
3. Reverse numbers using formulas
Download link:
http://www.box.net/shared/dbipvsg350#Reverse_Cells
@ Robert --- Thanx for soln. with 3 cheers !
But little hard to digest the logic of circular references. Cud u pls elaborate.
Ketan,
As I said above this might be too complicated to explain in a comment. But I will do my best and try to cut it short:
A circular reference means a formula referring back to its own value (directly or indirectly), e.g. you have the formula ‘A1+A2’ in cell A1. Usually Excel formulas with circular references do not run and come back with errors. Nevertheless you can take advantage of circular references for calculations that need iterative i.e. repetitive calculations.
First you have to enable Excel to do iterations. Go to Tools | Options | Calculation tab, check the iteration check box and set the maximum iterations to e.g. 100. By doing this you tell Excel to ignore the circular reference and calculate the formula 100 times (100 iterations).
In our workbook: The formula in B1 is =IF (B1=100, 1, B1+1). This is the help formula to run iterations from 1 to 100.
The formula in C1 is =IF (B1=1,"", IF (B1-1>LEN(A1), C1, MID (A1,B1-1,1)&C1))
If B1 = 1 the formula returns an empty string. This is the initialization of our new string as an empty string in the first iteration.
If B1 - 1 is larger than the length of our string in A1, the formula brings back the actual value of the C1 (the number of the actual iteration minus 1 is higher than the length of our string). The -1 is necessary because our first iteration is used for initializing our result with “” (see above). The second iteration handles the first letter of our string.
The else-part of the second IF clause MID (A1, B1-1, ) & C1 performs the calculations for reversing the string. This is the hard part. Maybe showing the results of the iterations will help to understand. Assume we have “ABCD” in cell A1. The iterations and the formula in C1 work as follows:
Iteration 1: C1 = “”
Iteration 2: C1 = “A” & “”
(“A” is the result of the MID-formula, “” is the result in C1 after iteration 1)
Iteration 3: C1 = “B” & “A”
(“B” is the result of the MID-formula, “A” is the result in C1 after iteration 2)
Iteration 4: C1 = “C” & “BA”
(“C” is the result of the MID-formula, “BA” is the result in C1 after iteration 3)
Iteration 5: C1 = “D” & “CBA”
That’s it.
To be honest, I am not sure that this was a good explanation, but maybe Chandoo will be so kind to publish a better description of this technique in a post some later day.
Last but not least: I do not recommend using circular references for reversing texts. It is hard to understand, error prone, complicated to debug, slow and irritating other users. I prefer the simple VBA-based user defined function (see the second worksheet of the workbook). It is simple, fast and easy to understand.
@Robert-- Thax for explanation ! I only did not understand how the iteration start. But from above explanation(in option), i got it. All other equations are understood. I had tried in the number with decimal also and found it works properly.
3 more cheers for giving useful tip ! i m sure such unusal / unexpected problem will enhance the knowledge of each other.
[...] 37. To get name from initials from a name, use IF(), FIND(), LEN() and SUBSTITUTE() formulas… Get Full Tip 38. To get proper fraction from a number (for eg 1/3 from 6/18), use =text(fraction, [...]
dear all
i want to convert number to text (words) in arabic language to use it for printing cheque
using VBA (MS ACCESS)
could you pls help me
Dear Chandoo,
I want to get one formula from you. I hope you help me in this problem.
IN Excel list of names are there in a column, some are completely upper case and some are proper case.
I want to separate or highlight the uppercase names in the column. How to do it?
Please clear my question. Thank you in advance to give the solution.
Regards,
Praveen.D
@Praveen... good question. here is how you can solve it.
Select all the cells, go to conditional formatting and add a rule. Select rule type as "use formula to determine which cells to highlight"
now, assuming your data is in cells A1:A10,
write =EXACT(UPPER(A1),A1) and set the formatting. This will highlight all the cells that are in upper case.
Hey,
I would like to know how to get initials of only the first and the middle names but the last name shouldnt have any initials. Also, some names in the list have just the first and last names while others have first, middle and last.. Could you please help me out with this?
regards,
Sobz
initial formulas
[...] 37. To get name from initials from a name, use IF(), FIND(), LEN() and SUBSTITUTE() formulas… Get Full Tip 38. To get proper fraction from a number (for eg 1/3 from 6/18), use =text(fraction, “?/?”) [...]
Hello,
You Formula was very helpful however i have would also like to know can we add SPACE in between the INITIALS for Example John Smith i want Initials as J S
Got it this worked...=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)))
This Formula work =UPPER(LEFT(A1,1)&LEFT(MID(A1,FIND(" ",A1,1)+1,1),1)&LEFT(MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,1),1))
how to use random formula for each and every employees in 2003 excel. Please help me with easy examples
Excellent formula sorted me out for work question made me look a genius. Thanks man. Martin (England)
I have a list with: Last Name, First Name, Middle Name. Is there any way I can use a formula to create proper initials?
Vincent
=LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)&MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1)
Ultimate Formula for this
=UPPER(LEFT(A1,1)&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>0,LEFT(MID(A1,FIND(" ",A1,1)+1,1),1),"")&IF(LEN(A1)-LEN(SUBSTITUTE(A1," ",""))>1,LEFT(MID(A1,FIND(" ",A1,FIND(" ",A1,1)+1)+1,1),1),""))
What if the name is in one cell and last name first. I want an initial of frist name and last name.
Smith, Jason = JS
How will I do this?
@Jina
=LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)
@Jina
=LEFT(A1,1)&MID(A1,FIND(" ",A1)+1,1)
Appreciate your help!
Bro I need a help!
I need to get the initials of more than three words in a cell but here is only for three words.Please help bro...
[…] Initials from names […]
Ok, so the first formula works well, but the spreadsheet I'm using has the name in this format: LAST, FIRST M and I don't want to change that since it's used like that in other places.
And, to make matters worse, some people have 2 last names with no hyphen, so I came up with this formula to handle the situation and make it blank if no situation works:
=IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=0,LEFT(B2,1),IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=1,MID(B2,FIND(" ",B2)+1,1)&LEFT(B2,1),IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=2,MID(B2,FIND(" ",B2)+1,1)&MID(B2,FIND(" ",B2,FIND(" ",B2)+1)+1,1)&LEFT(B2,1),IF(LEN(B2)-LEN(SUBSTITUTE(B2," ",""))=3,MID(B2,SEARCH(CHAR(127),SUBSTITUTE(B2," ",CHAR(127),2))+1,1)&MID(B2,SEARCH(CHAR(127),SUBSTITUTE(B2," ",CHAR(127),3))+1,1)&LEFT(B2,1),""))))
Thanks for the help!
I need to write a initial formula first name in column and last name in another column .
my attempt :
=left(a1,1)&(B1,1)
doesn't work
Thank you for a great formula. Is there a way I can get rid of the caps and make them lowercase?
Hi all
I tried the formula above and it works as it should but, i need the following. i want to be able to create / suggest an e-mail address from a full name e.g. if the names in full are, "Pieter Johannes Brandt Fourie", i would like to like excel to suggest "FouriePJB@company.com". Any suggestions?
regards
I believe what you posted made a bunch of sense. But, think on this, what if you were to write a killer
headline? I ain't saying your content isn't good, but what if you added a post title that grabbed folk's attention? I
mean Extract Initials from a Name using Excel Formulas | Chandoo.org - Learn Microsoft Excel Online is a little
boring. You might look at Yahoo's home page and note how they create news titles
to get people to open the links. You might try
adding a video or a picture or two to get readers interested about everything've written. In my opinion, it might bring your posts a
little livelier.
Hi. I am trying to create a csv to import into my records database, and I need to pull student names together to get a primary addressee. I know how to concatenate first, middle, and last name along with titles, but I'm not finding how to abbreviate the middle initial and add a period. I see your formulas to make initials, but not what I need. Thanks!
Assuming every student has a middle name and first name is column A, middle is column B and last is column C you can use the following.
=A5&" "&LEFT(B5,1)&". "&C5
If some students don't have a middle name you need to use something like this.
=IF(B8="",A8&" "&C8,A8&" "&LEFT(B8,1)&". "&C8)
Thank you!
I am using this formula to pull first and last initial from the name in a cell:
=LEFT(B2,1)&MID(B2,FIND(" ",B2,1),2)
How do I skip blank cells? Thank you.
I am using this formula to pull first and last initial from a full name:
=LEFT(B2,1)&MID(B2,FIND(" ",B2,1),2)
How do I also skip blank cells so that a value error message won't appear? Thank you.
@Gretchen
=if(len(b2)>0,LEFT(B2,1)&MID(B2,FIND(" ",B2,1),2),"")
@Gretchen
Chandoo.org is a Moderated Forum
As such comments don't appear until they are approved
So there is No need to post twice
Hui...
Thank you. The dual post was mistake.
Is there a way to automatically run a list of dates in a single column, that will show the month as an abbreviation (ie..JUL) and the dates for each weekday. Such as JUL 4-8 in cell B5 and then JUL 11-15 in cell B6, and so on? Thank you. I can type it manually but thought there might be a formula that can do this.
Thank you for this formula, it did exactly what I needed. Sorry for the delay in responding.
Interesting post . I learned a lot from the points . Does someone know where I can get access to a blank MN MSBA Form No. 16 version to fill out ?
Can I ask for something a tiny bit different?
I need to extract the first letter of each word of a phrase plus the last letter of the phrase (and not capitalized). So...
He will not be able to
would be...
hwnbato
Another example:
fruit of the loom = fotlm
This is for use in a text expander.
Could you provide that formula? I have an enormous list of phrases to abbreviate.
This might be what you're looking for.
https://www.extendoffice.com/documents/excel/1580-excel-extract-first-letter-of-each-word.html
Wrap the formula with =LOWER() to convert to lowercase.
@Cindy
Try this VBA User Defined Function
Function PullLetters(text) As String
'extract the first letters of each word in a sentence or string
mystring = Left(text, 1)
For i = 2 To Len(text) - 1
If Mid(text, i, 1) = " " Then
mystring = mystring & Mid(text, i + 1, 1)
End If
Next i
PullLetters = WorksheetFunction.Substitute(UCase(mystring), " ", "")
PullLetters = PullLetters + Right(text, 1)
End Function
To use it copy it into a code module in VBA
In Excel type =PullLetters(A2)
Thank you so much!! You can imagine how much time that will save me if it works! I really appreciate it.
Thank you so much! This is a most awesome formula.
=LEFT(H7,1) & IFERROR(MID(H7,SEARCH(" ",H7)+1,1),"") & IFERROR(MID(H7,SEARCH(" ",H7,SEARCH(" ",H7)+1)+1,1),"")
works upto 3 words...!! smaller version