Formula To Create Name Initials Where Last Name Listed First
Aug 3, 2014
I am looking to create a formula to get the initials of a name, where the the last name is listed first, followed by a comma.
Now, the names could be full names or initials.
So, it could be something like: Smith, John R - result would be JRSSmith, John Robert - result would be JRSSmith, John Robert Michael - result would be JRMS There were similar formulas that I found here, but these only got the last letter of the last name - in this case the following formulas would have resulted (with the above examples) in: JTS or the last example with Michael, JLS.
=UPPER(MID(A1,FIND(" ",SUBSTITUTE(A1," ",REPT(" ",2)))+1,1)&RIGHT(A1)&LEFT(A1))
=UPPER(MID(A1,FIND(" ",A1)+1,1)&RIGHT(A1)&LEFT(A1))
Note: While I am using Excel 2010, there could be users that are still using 2003.
View 5 Replies
ADVERTISEMENT
Oct 16, 2008
I have a table that lists the months of the year down from cell A2:A13, and days of the week along row from cell B1:H1. The data in between (cell B2:H13) is pulled through from elsewhere in the workbook and is in number format.
What I basically want to do is create another worksheet with the months of the years listed down column A, and in column B, for each month, I want the first day of the week where the value in the original table is more than zero, and in column C the second day of the week where the value is more than zero. I really hope that makes sense, was quite difficult to explain!
View 2 Replies
View Related
Mar 8, 2008
An application I use at work exports names in a first name-last name format. At this point I have a list of about 315 names in this format. Is there a way I can convert these names to a last name-first name format? I need to export these data on a daily basis, so I'll need to perform this conversion every day. I did a search for this, but didn't find anything.
View 9 Replies
View Related
May 5, 2012
I am trying to get periods added to initials.
Colum A
(Can be up to 4 caracters)
FJM
ML
B
GHTJ
What I need in colum B
F.J.M.
M.L.
B.
G.H.T.J.
What formula can I use in the B cells to get this result?
View 7 Replies
View Related
Nov 28, 2009
I'm new to the forums, though I've solved a couple of my problems by lurking here.
I'm currently having a problem with a worksheet I've been working on at work. I'm not very experienced in Exel and have inherited a worksheet that keeps track of the machines we do maintenance on during the year. What I have now is a spreadsheet with columns alternating between initials and date, and rows with the machine names all the way down. It looks similar to this :
Name of machine Initials date initials date initials date initials date etc.
machine 1
machine 2
machine 3
machine 4
...
machine 121
I'd like to keep this format, but we need to keep track of who does what within a certain week, which means I need to count the number of instances of an initial, then compare it with the date in the column next to it, which I don't think would be a problem with just two columns, but I'm having problems wrapping my brain around how to do so with 20-30 columns.
I'm using date ranges in some of my calculation formulas, which I think necessitates the date being in a separate column than the text.
View 9 Replies
View Related
Apr 20, 2007
I have 2 columns of Surnames and Initials and I am trying to concatenate an initial (if required) with a surname or return a “blank” if the cell(s) is empty, in other words in cell AC20 I have a surname Smith and in AG20 an initial A which I only enter into the cell if there are identical surnames but when the cells are blank I wish to return a blank not a zero.
I have tried =IF( COUNTBLANK(AC20:AG20)=2,"",AG20&""&AC20) and =IF(AG20="",AC20,IF(AC20>"",AG20&" "&AC20)) but the first one won’t return the initial and the last one works but both return a zero when blank.
View 5 Replies
View Related
Apr 22, 2012
cell a1 is blank
cell a2= "sb"
cell a3= "db"
cell a4 is blank
cell a5= "rm"
cell a6= "kw"
I want to randomly pick 1 set of initials from the 4 listed in the 6 cells and put the result in cell a7.
View 1 Replies
View Related
Sep 8, 2009
I just bought a database and I would say at least 20% of the names in the database have an initial after the first name. I need to have a clean first and last name list. Is there a formula that can delete the initials? I am working on a variable data print project that uses their first name in the message so I cannot have their middle initial.
So like in one example her first name is listed as Susan F. I want it to just be Susan. Another example is T. Jones as a last name, I want it to be just Jones.
View 9 Replies
View Related
Oct 4, 2009
Can anyone tell me if its possible to input some ones initials into a cell and when you hit enter, their full name is displayed instead of the initials:
ie input FB and the same cell displays Fred Bloggs?
View 6 Replies
View Related
May 29, 2014
I have QC checklist with a cell at the bottom for the user's initials. We have 8 users, and I would like to create a way that each user can enter their pin and have their initials come up in the cell. Ideally, there would be no way to the other users to know each other's pins or find out in the spreadsheet. Do you think it is do-able using VBA and how they would approach this?
View 1 Replies
View Related
Jul 7, 2009
I am trying to make up a workbook for a friend for their daily food intake. The formulas were working fine but she was hoping to be able to just enter the food and have the rest of the information automatically entered.
I was having no problem with it using a vlookup formula for the foods and a data validation drop down menu but when it comes to the final formula I am getting a #VALUE! error in that column unless there is a food listed.
I have attached to workbook. If you notice in the Sunday Tab I have the problem but Monday is fine since I have not added the vlookup formula. Is there a way to have it not show anything like in Monday when there is nothing entered?
View 2 Replies
View Related
Feb 16, 2010
i know by the formula =combin(49,6) how many combination of 6 numbers there are from a available 49 numbers (13983816).
how do i manage to get all combinations listed in a excel spreadsheet? I would want the numbers in individual cells. For example, Row 1 would show 6 numbers individually from A1 - F1.
I do realise that excel does not have enough rows to do this so may have to continue on other sheets.
View 14 Replies
View Related
Mar 16, 2007
I have a list of jobs names on one sheet and there are certain ones that i would like to put on a list on a second sheet.
what I would like to do is put a "1" in a cell next to the job names that I want on the other sheet and have them automatically listed.
View 10 Replies
View Related
May 31, 2012
I need a macro to find for example
Bigg, Kelly
Bigg, Kelly
Bigg, Kelly (find the last name listed in the column) then move over (0,2) and copy - paste into a different spreadsheet.
Where I am mainly stuck is it locating the last name listed in the file - if the name is listed multiple times.
View 2 Replies
View Related
Sep 11, 2008
I do have one issue I need to work on with a VLOOKUP formula.
I have a list of cities and counties that the VLOOKUP goes and looks at to pull information from.
The problem is that when there is a city and county with the same name (Example: Boulder and Boulder County) VLOOKUP always seems to jsut grab the one that is listed first in the list.
When you use the city name of Boulder, VLOOKUP always retruns the information for Boulder County.
Here is my forumula:
=VLOOKUP(qtProjAddress3,'City Mileage and Tax Rates'!A2:C518,2)
Is there something I can add to this formula that will allow it to return the proper city or county when the names are similar?
View 9 Replies
View Related
Jul 31, 2014
I have a spreadsheet which is used for tracking work items for a team of people. On one sheet I have a column of dates and rows of named 'tasks' against them. I need to identify the first and last dates at which a 'task' is listed.
My spreadsheet is attached.
On sheet2 K2 I want to see the first / earliest date where 'Fish' occurs on sheet1
On sheet2 L2 I want to see the last / latest date where 'Fish' occurs on sheet1
I have tried using SUMPRODUCT MAX / MIN but I got myself in a muddle!!
TrackerRH.xlsx‎
View 5 Replies
View Related
Nov 17, 2013
The current method I'm using to get the results I need in Excel are a bit manual and excessive. is a simple all in one formula for what I'm trying to do,
The data is 3 rows. We can also assume the data is in the first three columns starting from A1.
698
885
499
As you can see in the above set from 0 through 9, the numbers that are not listed are 0,1,2,3,7
And As you can see the numbers that are listed are 4,5,6,8,9.
How do I find and show which numbers from 0 - 9 are not in the set.
How do I show the number that are listed in the set Example:
Note: the special characters aren't needed.
698 4,5,6,8,9 --- 0,1,2,3,7
885
499
Note the the evaluation is for every three rows. So the three rows in the example are from a table with 150 rows. Each row begins a new evaluation for the next three.....Example:
698 and the following two starts the next set
885 and the following two starts the next set
499 and the following two starts the next set
and so on ......
View 6 Replies
View Related
Aug 8, 2014
I need a code that will copy a list in the same order from different sheets and will paste special and transpose it on the summary sheet.
View 1 Replies
View Related
Aug 14, 2012
I am creating a load file for software from Excel. Based on our planning calendar and delivery time, I need to calculate the Delivery day.
Excel File as follows:
Planned Delivery Time Planned Order Day Planned Delivery Day
8 days Thursday (Need a formula that says 8 days from Thursday would be Friday)
13 days Tuesday (Need a formula that says 13 days from Tuesday would be Monday)
I have attempted with simple formulas adding time, complex formulas, and cannot find a simple solution that works for days of the week.
View 3 Replies
View Related
Apr 9, 2007
I have a project I need to get done, and I've done most of it. But, there are a few things in VBA I'm unfamiliar with, and I was wondering if I could send you the file so you could take a look at it, and see if you're able to do it. This involves VBA. Honestly shouldn't be too too difficult, but something beyond what I can do. I have the specifications listed already, and I am ready to send it to you. Anyone feel comfortable taking a look at it?
View 9 Replies
View Related
Nov 16, 2007
I am attempting to create a macro that will loop through a range, each cell of which contains a worksheet name which needs to be activated in turn. My limited knowledge says that either of the two techniques below should work, but they do not.
Sub SelectSheet()
For i = 2 To 50
Sheets(Range(("J" & i))).Select
Next
End Sub
OR
Sub SelectSheetv2()
For i = 2 To 50
Sheets((Cells(i, 10))).Select
Next
End Sub
View 3 Replies
View Related
Oct 8, 2009
i have all the states listed in A1 and i would like to seperate them into east and west.
example if a1 have FL and want a52 to say East and if i put CA it would say West.
View 9 Replies
View Related
Oct 2, 2006
I have a Combobox with it's 'RowSource' set to two columns x 1500 rows on a spreadsheet. The Combobox is set to 'fmMatchEntryComplete'. When the user types in invalid text I get an "Invalid Value Property" error. I would like to validate the Combobox so that the user cannot type text other than available in the list, or not allow for the focus to be taken away away unless the item is a match to the list. At present due to the interaction of other controls on the userform, the only way to clear the error is by pressing the 'Escape key'. I have a button designed to reset the 'RowSource' of the Combobox but even after adding a 'MouseMove' event to this button with code
Application.SendKeys "{ESC}"
View 4 Replies
View Related
Oct 20, 2006
I have a list of names on my first sheet, and I want to mirror these on a second sheet, but at the start of a repeating table containing data releating to each named person. eg. I want a Name in
'Sheet 1'!A4
to display in
'Sheet 2'!A5
, and then the name in
'Sheet 1'!A5
to display in
'Sheet 2'!A32
View 3 Replies
View Related
May 20, 2008
I have a "activity log" sheet which records the date in column A, the activity in column C and the name of the customer that was contacted in column D. There may be multiple entries of a customer's name in column D.
In a separate sheet, I want to find the latest date from the "activity log" sheet that a each customer was contacted and the specific action on that date. In this sheet, the client name will be in column A, and I want to record the last contact date for the client in the same row in column J and the type of contact in column K.
View 4 Replies
View Related
Jun 7, 2013
How to get a formula that will substitute every X with the actual type of meat. For instance, under Monday column, my table should list Liver, Pork, Mutton and Lamb Stew instead of X.
Mon
Tues
Wed
Thurs
Friday
Liver
X
X
[Code] ........
View 2 Replies
View Related
Sep 10, 2013
I use Excel 2003 & I am trying to design a worksheet to keep track of signs I make & their order numbers. I want the names listed alphabetically. When I add a new name & I arrange the column alphabetically, the columns with the order numbers do not relocate along with the names column.
View 5 Replies
View Related
Mar 30, 2008
I have attached an excel sheet which i wanted to write macro as an attachment. .
when I run a macro an email has to be sent to each persons listed in column A with Voucher # in the subject line and URL in the body of the message after the URL, the body of the message will remain same for all the emails:
so I have typed the body of the message in 2nd sheet:
View 14 Replies
View Related
Sep 16, 2012
I have below set of value in Sheet 1 (it has category & Name), whenever I open the Sheet 2 & Sheet 3, unique values should get automatically posted in D column..
Category
Names
Pet Animal
Dog
Pet Animal
Cat
Wild Animal
Elephant
[code]....
I have two requirements on this..
1. Unique names should listed in cell D of Sheet 2
2. Unique value of both category & name should listed in Cell D of Sheet 3
View 1 Replies
View Related
May 28, 2009
I have seen in the forums where lists are created by refering to column values in other worksheets.If you create a list by entering text values can you make other lists dependant to those values and can those lists also be made up of text lists?
All the examples I have seen here use lists that exist in some other worksheet
I use lists which use text values directly and not from a worksheet. I'm wondering how to make a secondary/sub list dependant on each value selected so say if text value "A" is selected in list 1 then this only allows the list for "A" to be available for list 2 in its corresponding cell (the next one along)
View 9 Replies
View Related
