Separate First And Last Names Entered In One Cell Into Two Different Cells
The database that I'm working on divides patient names into three separate columns: lastName, firstName, midInit. My department has several old spreadsheets that have data my boss suddenly wants included in my database. The trouble is, these old spreadsheets have the patient's last name, first name, and middle initial entered into the same column.
There are at least 200 hundred names in these spreadsheets. I absolutely do not want to go back and separate the names into separate columns by hand.
Is there any way to get Excel to take the names entered into one cell and split them into three cells?
I've attached a spreadsheet example that shows what I need to do.
View Complete Thread with Replies
Related Forum Messages:
Parsing Names In Separate Cells
a smart formula (no VBA) that will reverse the order of a list of names - parsing into separate columns.
The formula needs to be able to recognize/distinguish that if a comma(s) is present, then the names are listed as last, first middle. If there is a period this means the name has a middle initial or that a suffix is present. So,if the names are listed as last, first and middle then in the next columns will have in the proper order first name, middle name or initial (keep the period) and last name (loosing the comma of course).
If the original name order is in normal order - first middle and last then the formula should just pass through without any change. Would need to preserve generational titles such as Jr., Sr., III etc. Not sure where the best place for the generational titles should end up. Probably as part of the last name. See examples:
Col ACol BCol CCol D Col E
William J. ClintonWilliam J. Clinton
Churchill, Winston Leonard Spencer Winston Leonard Spencer Churhill
Bates, JohnJohn Bates
James Knoll PolkJames Knoll Polk
Taft, William H.William H. Taft
Carter, James, Earl, Jr.James Earl Carter Jr.
Rockefeller, John Davidson, IVJohn Davison Rockefeller IV
Gates, William, H., IIIWilliam H. Gates III
St. James, Michael, Ken Michael Ken St. James
I tried using many variations of FIND, SEARCH, LEN and searching all the Excel boards to no avail.
Separate Names From Dates & Commas In Cell
I have a file with list of names in the attched file.They are not in the same format coming from the source in my company. I need only the names in the separete column without the dates and the commas. The text is not in the same format. Looking at the data, can someone help me make a formula to separate only the names from the the entry.
the data is huge and it takes lots of hours to clean this data.
I am attachign the excel file for reference.
Splitting Names Into 2 Separate Columns
I have two columns in my excel sheet, the first column has the Second name, First Name and the second column has the Date of birth.
Now what I'm after is two columns really, the first column should show the Furst name Second Name (space in between with no Comma) and the Second column should show the First Initial from the first name, the first initial from the second name and then the date of birth.
Locking Cells Depending On What Is Entered In Another Cell
I'd like to lock some cells depending on what is put in a particular cell. As shown by the file attached, if the first column (Amendments) has a "no" in A4, I'd like to lock out cells B4:D4. (The sheet will be protected). If it's a "yes" then the cells are unlocked. I'd like to then apply this to all subsequent rows (i.e. lock B5:D5 if a "no" in A5 etc.)
Using Data In Two Cells To ID Separate Sheet And Cell
I use Excel 2003. I need help building a macro, please, that will copy data to a specific page in another workbook based on two criteria. Here's the deal:
The data in Workbook A, Sheet 1, Cell A1 may contain the word ALPHA, BAKER, or CHARLIE. Cell A2 may contain the number 1, 2, or 3. Cell B1 contains the data I want to collect from various Workbook As and keep in a list to analyze.
Over in Workbook B, Sheets 1 through 3 are named ALPHA, BAKER, and CHARLIE. Rows A, B, and C are titled 1, 2, and 3.
How can I copy the data from Workbook A, identified as ALPHA 3, to it's place in Workbook B, Sheet ALPHA, Row C?
Furthermore, Workbook A is a one time form will be used many times. Thus, when I copy B1 to Workbook B, Sheet ALPHA, Row C, I need to paste the data in the first empty cell in the row.
Separate Comma Separated Cell Into Many Cells
I receive a large spread sheet weekly, one or the column contains comma separated values (CSV), I need to separate those values 1 per cell. Maximum CSV is 5; I would need to get the output to 5 cells next to them, Example,
(note, not all field contain 5 values, but they all contain at least one)
Cells With Instructions Which Disapper When Data Entered
I needed some help in creating a template for people. In the template, I want to create cells which instruct the user on what need to be filled in that cell.
For eg. go to www.m-w.com In the box, it says 'Enter word or phrase'. When you click to enter a word, this line vanishes. I want to create a similar thing in Excel.
In Excel, for eg, in a cell it should say 'Enter Name' and when the user enter his name, this instruction should vanish. Also, this instruction should be in GREY and when they user enters his name it should be BOLD BLUE.
How To Unhide Rows When Data Is Entered Into Cells
I have been searching the internet and forums for days now looking for and trying out macros and scripts that will automatically unhide rows once data has been entered into a cell. The data entered is a link to a cell in another sheet. Everything I have tried is close to what I am looking for but it does not quite work, and because I really don't understand the programming details, I don't know what to change in the script to make it work for my spreadsheet.
create a macro/script to do the following:
Various rows in my summary sheet will be hidden as they will probably not be used, but in the off chance that data will be displayed in them, I need them to automatically unhide. The data that might be displayed in them is a link from monthly record sheets i.e. "=Jan!F4" or "=Feb!F32"
And because I am taking data from a 12 month period, I need the rows to unhide if any data is displayed between cell range of F5:AC1705. It is a large spreadsheet of data and it is all linked to 12 other sheets.
Unfortunately, I am unable to attach the file as it is too large.
Automatically Lock Cells When Data Entered
I am trying to apply a conditional cell lock to a worksheet. I am using Excel 2003, not 2007.
In column J, I have a list validation being used. The default value is "Not Claimed", I want to lock the cell from being edited once the value has been changed to any other value on the list.
The worksheet will be password protected to prevent entries into static fields (A:I & N:P).
I also would like to give the user the ability to unlock a specific cell, not the entire worksheet, by entering a password or something if they accidently choose a wrong value from the list.
I have column Q and beyond available if I need to have the password entered somewhere else.
I have attached a sample of the file I am working with.
Printing Name And Time In Separate Cells
In my Workbook, Sheet 1 i have Cells B2, C2, D2, H2 all with Name, Age, M/F and Fastest time,
on Sheet 3 i have Cells A4, B4 with Under 20 Male and Time.
What i want to know is how do i go about searching the Age and M/F cells and printing the names of the people who fit the criteria ( for under 20 male it would be males aged 16, 17, 18 and 19) into A4 and their time into B4
to make it worse i want to order the times/names in fastest to slowest time .
i can get half way there but i cant seem to get the formula to pick up on >=16 and
Extracting Words In Separate Cells
I need a formula which would from one cell extract the words in separate cells. I have probelms because the words in the sentence in the starting cell are not separate by spaces but dots. For example: How.do.I.extract.words.from.this.sentence?
Copy Cells Into Workbooks Names Mapped From Copy Cell
1. I have a list of data (Collated Data)
2. For every row in collated data I want to export the cells into a corresponding cell in my predetermined workbook (TAF Form), i.e, Cell C1 on Collated data goes into Cell D3 on TAF Form, cell D1 to cell I3 etc etc
3. Once all cells in one row have been copied into the TAF Form I want that TAF Form to save as "TAF Form & Employee Name" (which would come from cell D3).
4. I then want "TAF Form & Employee Name" to close.
5. I then want the Macro to do the same thing for Row 2, copy the cells, save the form, close the form
6. I want to do this for every row that I have (which varies).
Is this possible? If you have any more questions in terms of what I need, don't hesitate to ask.
Function: Returned Values To Be Entered Into Different Cells In A Column
I am trying to write a formula that will look at an array (containing text strings) and then for each occurrence of a particular value return the text string from another cell in another column but which matches the row of the occurring value. I would prefer all returned values to be entered into different cells in a column but I would be happy if they were all in one cell separated by a comma or whatever.
Alphabetize Separate Cells Of Data In Same Row
I have a worksheet with 6,500 rows of data. In each row, there are up to 6 cells containing a name in "Last, First" format. I need to alphabetize the data from these 6 cells so they go across the row in order left to right, alpha by last name.
Example of my data layout:
Column 1 Column 2 Column 3
Row 1 Jackson, Bob / Anderson, Lisa / Cardinal, Mike
Row 2 Tomlinson, Steve / Dodge, Sarah / White, Brian
I need them to be in this order:
Column 1 Column 2 Column 3
Row 1 Anderson, Lisa / Cardinal, Mike / Jackson, Bob
Row 2 Dodge, Sarah / Tomlinson, Steve / White, Brian
Is there a formula I can apply that will pull from only the portion of the data before the comma in each cell? Or do I need to separate out all of the names so the first and last names are in separate columns for this to work?
Separate Comma Delimited Cells To New Rows
I have a 5K rows of data, each including a cell (AD) of multiple number values, separated by comma. I would like to create a new row for each unique number in column AD such that there remains only one unique value for every AD cell.
If possible, I would prefer an in-cell formula rather than a macro.
Compare Specific Cells From Three Separate Worksheets
Is there a way to compare specific data from three separate worksheets to see if they are equal?
I receive three daily reports that contain some of the same information that must match in order to correctly compile an Executive Report. Currently, I am doing this manually. Is there any way to automate the process to have a warning or something similar pop up if the numbers do not match?
Creating Separate Reports In Separate Spreadsheets
I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet.
I want to do this automatically.
I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date).
Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it.
Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data *
Copy Data *
Create a New Spreadsheet named (Person's Name) mm-dd.xls
Paste Data (values, formats, column widths) to the new spreadsheet *
Delete Several Columns from the new spreadsheet *
Rename the sheet it's been pasted to
Re-filter data (new filter) *
Copy Data *
Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 *
Set the header of the spreadsheet with (Person's Name) and the date
Save the new spreadsheet
There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...
When A Date Entered On A Specific Cell, Automatically Enter A Text In Other Cell
I have 6 Headings in excel named...
"A" in cell A1, B in B1, "C" in C1, "D" in D1, "E" in E1 and "F" in F1.
There are two projects.
Project 1 has phase A, D & F and Project 2 has phase A, B, C, D & E.
My Specification follows...
1). Take Prject 1 - Which starts from A...in cell A2 I will keyin "A". When the phase comes to an end I will key in the end date of the phase. As soon as I key in the end date in cell A2 Letter D should automatically appear in the cell D2 and when Phase D comes to an end I will key in the end date in Cell D2 which should automatically keyin F in the cell F2. and is the same case for Project 2.
Converting Pdf Cut And Pasted Data Into Separate Cells
I have rxd a pdf file that I need to manipulate I have copied the data into excel and now need it in individual cells:? The data looks like this: 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03. this is:
phone number calling | date called | time called | number called | duration in minutes | cost of call. If you're out there I'd be more than happy to hear from you. the cells to cut the data into will be formatted to take the data in the correct form. 02392950067 19/12/2008 14:33:35 02392950053 Portsmouth 1.9 £0.03
phone number calling | date called | time called | number called | duration in minutes | cost of call.
Block Anything From Being Entered In Cell
I have a log that information gets enter into by rows. When one rows info is entered a "Start" button is to be clicked and a macro runs and does different calculations. I want a way to not let the next rows information to be entered until the "Start" button is clicked on or Is there a way to have the code run automatically when the user has entered the info in the last cell of the row and goes back to the first cell in the next row ?
Search Contents Of Cells In Row From List Of Text Values On A Separate Sheet
If this has been requested already, I apologize, please direct me to the right thread and flog me with USB cable. I've been searching the forum for an answer for a couple of hours now for the answer to this:
I have a list of words in column A on a sheet and I'm trying to write a formula on another sheet that will check all cells on a pre-populated row and return a value if any of the words from the list on are contained in any of the cells in the row. I've attached an example spreadsheet
Cell Colour Depending On Value Entered
I found the code below to do almost exactly what i want. However, i would like to know if it can be modified to only affect a specific cell range. In my case, the cell range is B9:AF37.
Option Compare Text 'A=a, B=b, ... Z=z
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cell As Range
Dim Rng1 As Range
On Error Resume Next
Set Rng1 = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas, 1)
On Error Goto 0
If Rng1 Is Nothing Then
Set Rng1 = Range(Target.Address)
Set Rng1 = Union(Range(Target.Address), Rng1)
For Each Cell In Rng1
Call Macro On Any Value Being Entered In A Cell
I'm trying to create a macro that will check that an Swedish corporate identification number (CIN) is correct. Swedish CIN conist of 10 numbers and are always written 123456-7890. The last number is a control digit to see if the CIN is correct. I've managed to get the macro working but I am not able to get it to work automatically. that is I want the macro to start running immediately after you have entered a companys CIN. In my template you are supposed to enter the CIN in cell A1, and I want the macro to check that immedately when you have pressed "enter". I can not get it to work. the macro works when I do it manually, that is Tools>Macro>Macro..>Run. I've read all about it here but I guess I just don't get it
Separate Text In One Cell
I am try to separate my data from A1 to B1,C1, D1....... Here is what it look like in A1
C102, C110, C114, C116, C118, C120, C125, C128, C130, C131, C132, C134, C135, C139, C140, C143, C144, C19, C21, C22, C27, C30, C38, C40, C50, C56, C57, C59, C6, C60, C61, C69, C85, C88, C90, C94, C98
I want to separate them into several cells. Each cell can only have 30 characters or less. You can not cut it in the middle of the data.
After your separate B1 should be "C102, C110, C114, C116, C118," (30 characters)
C1 should be "C120, C125, C128, C130, C131," (30 Characters)
D1 should be "C132, C134, C135, C139, C140,"(30 characters)
E1 should be.......... till the end.
I try to several functions too. But it does not works.
Separate Information From Cell
how do i separate information like this:-
let say its in cell A1 & i want first information before the First , to appear in B1 which is 01/08/2007
then the information before the second , to appear in C1 which is 09:54:27
and so on
Separate Address From Cell
I have a dataset with full addreses listed in one cell. I would like to use "text to columns" to separate the data but am unable to use the text separater in the wizard. When I open the text to columns wizard the data appears to be divided with a "square" but I cannot add it as "other" for the delimeter. see the attached.
Separate Cell Change Based On Value In Cell
In my spreadsheet, certain transactions are labeled as "Fees" in Row G. I need any amounts in Row D to be negative if they are labeled as "Fees" in Row G.
I could simply find them manually and make them negative, but the logic needed in this question will help me with similar problems I am facing.
I am thinking it is probably some combination of the "IF" command.
2007 (Count Words Entered In Cell)
I'm working on a project and need some assistance. Using Excel 2007, how do I add up the total of the word "apples" to a cell.
Example: If Cell A1,A10,A25,A30 all have the word apple entered, how do I add them up in lets say cell B10.
Also, I need to do it where I can enter multiple words in 1 cell and have it organized according to how many times the word is used.
Colour Change Cell If Number Entered
I want to incoroprate into my Workbook a sheet with a list of phone numbers, that sheet can be added to and more numbers entered if necessary and will be called "DATA"
The questions is this. If one of those numbers in the "DATA" sheet is entered in any other sheet in the workbook can the cell that number has been written on (not the one on the data sheet) be made to change colour?
Automatically Tick Cell If Entered Value Matches
I have attached a spreadsheet which, when you enter a matching value in the vehicle column, the cell that matches the vehicle name in both instances (column and row) is ticked. Think I am wanting to use a worksheet_calculate function but cannot figure out how to write the appropriate lookup in VBA. I do not want a formula in the cell.