Question asked and responded several times. If original strings are in column B, Insert =IF(ISBLANK(B),',LEFT(B,SEARCH(' ',B)-1)) in column C and =IF(ISBLANK(B),',RIGHT(B,LEN(B)-SEARCH(' ',B))) in column D It's just a starting point which assumes that the fiirst name is a single one. It is difficult to leave a spreadsheet formula recognize that Jean Jacques Rousseau as 'Jean Jacques' and 'Rousseau' and Jean de la Fontaine as 'Jean' and 'de la Fontaine' Yvan KOENIG (from FRANCE vendredi 19 septembre 2008 15:52:39). Question asked and responded several times. If original strings are in column B, Insert =IF(ISBLANK(B),',LEFT(B,SEARCH(' ',B)-1)) in column C and =IF(ISBLANK(B),',RIGHT(B,LEN(B)-SEARCH(' ',B))) in column D It's just a starting point which assumes that the fiirst name is a single one. It is difficult to leave a spreadsheet formula recognize that Jean Jacques Rousseau as 'Jean Jacques' and 'Rousseau' and Jean de la Fontaine as 'Jean' and 'de la Fontaine' Yvan KOENIG (from FRANCE vendredi 19 septembre 2008 15:52:39). =IF(ISBLANK(B),',LEFT(B,SEARCH(' ',B)-1)) =IF(ISBLANK(B),',RIGHT(B,LEN(B)-SEARCH(' ',B))) Yvans equation works great, a little more explanation if you need it.
![Popular Popular](/uploads/1/2/5/3/125364916/654560361.png)
The base functions you should research and learn for this are RIGHT, LEFT, LEN and SEARCH. ISBLANK is only really necessary if you might have empty cells in B that have no name.
The IF in his equation tests for that and places an empty string as an answer in that case. Otherwise you would most likely see an error show up instead. If and only if the value in B is not blank, then it runs the other equation, which searched for the space in the value, and uses that position to tell the LEFT command how many characters to grab (1 less than the result of search) If it is column D, it has to know how many total characters there are in the string, and subtract the number of the position of the space to find out how many characters from the right hand edge of the string to grab.
Hope this helps explain it better if you were still a little baffled, Jason. Apple Footer.
It's just a starting point which assumes that the fiirst name is a single one. Yvan KOENIG (from FRANCE vendredi 19 septembre 2008.
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple may provide or recommend responses as a possible solution based on the information provided; every potential issue may involve several factors not detailed in the conversations captured in an electronic forum and Apple can therefore provide no guarantee as to the efficacy of any proposed solutions on the community forums. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the.
89 comments 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. More on names and text formulas:,. 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. @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: I can't tell you whether this is true or not, I don't speak a word Portuguese. 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. 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. 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!! 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.xlsSheetName. @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: 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:. 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 xlfiles ab.xlssheet1'!a1 should 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.
Saleswestfebruary08.xls, salessouthfebruary08.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. 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!). The mistake is mine.
As Sudhir mentioned in the next comment. 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. 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. 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.xlsSheet1!$A$1:$B$10,2,0) Formula in C2: VLOOKUP (A2, NY.xlsSheet1!$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 CreateMyVlookupFormulas 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 & '.xlsSheet1'!$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 workbookopen or a worksheetcalculate 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. @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. @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, 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. 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-1LEN(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. 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. 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.