Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










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

Sponsored Links:

Related Forum Messages:
Separate The First And Lasts Names From One Cell Into Two Cells
This is where you introduce yourself? I make lists for my gf's dad's company using excel. One of my most excruciating tasks is to separate the first and lasts names from one cell into two cells. Does anyone know a quicker way to do this other than manually? I have 10,000 names to do and it's going to be hours if I do it by hand.

View Replies!   View Related
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
CherCher

I tried using many variations of FIND, SEARCH, LEN and searching all the Excel boards to no avail.

View Replies!   View Related
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.

View Replies!   View Related
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.


View Replies!   View Related
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.)

View Replies!   View Related
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.

View Replies!   View Related
Separate/Split Data In One Cell To X Cells
How do I split data in one cell into three cells?

example:

From

(A1)100 CARIBBEAN VILLAGE DR

To

(B1)100
(C1)CARIBBEAN VILLAGE
(D1)DR

Not all the data is the same, some have more words than others.

View Replies!   View Related
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,

Source data
(A1) 10,11,12,13,14

Output needed
(B1) 10
(C1) 11
(D1) 12
(E1) 13
(F1) 14

(note, not all field contain 5 values, but they all contain at least one)

View Replies!   View Related
Highlighted Cells (enter A Character And For Each Cell Within The Selection That Value Is Entered)
when i highlight any number of cells what i want to be able to do is enter a character and for each cell within the selection that value is entered.

i know this is possible by pressing CTRL + ENTER but is there a way i can code it onto the sheets module?

View Replies!   View Related
Combining Two Values In Two Separate Cells To Make A Cell Reference Or Index Refer.
I want to use a value in one cell as a row designation, and a value in another cell as a column designation. Ultimately, the values will be text which will refer to row and column headers. What formula would allow me to do this? example:

A1 contains B
B1 contains 2
B2 contains "tribbles"

An imaginary function might go like this........

View Replies!   View Related
Check All Cells Are Entered
I am using the followng code to check data is entered in a cell ...

View Replies!   View Related
Separate In Deferent Cells
when i copied data from text to excel it becomes all in one cell ,how can i separate in deferent cells ,file attached

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Macro To Lock Cells After Data Entered
I need a macro which will lock the cells after data is entered in the cell once. Cell should become non editable and should be only edited after a password is provided.

View Replies!   View Related
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

View Replies!   View Related
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?

View Replies!   View Related
Match 2 Separate Cells To A Table?
I would like a formula that will take the info from 2 cells (not adjacent to each other or in the same row) and then look these up in a table giving the answer in the 3rd column somewhere else on the worksheet.

View Replies!   View Related
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?

View Replies!   View Related
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.

View Replies!   View Related
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.

View Replies!   View Related
Listing The 2, 598, 960 Different Poker Hands In Separate Cells
Let's say My criteria is :

1 2 3 4 5 6 7 8 9 10 J Q K A (13 ranks of cards)
s c d h (4 different suits...s=spades c = clubs,etc)
5 would be the number of draws of 1 card to make a poker hand

How would I go about listing the 2, 598, 960 different poker hands in separate cells?

View Replies!   View Related
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.

View Replies!   View Related
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?

View Replies!   View Related
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.

View Replies!   View Related
Separate Date & Time Data Into 2 Different Cells
I have a cell with both date & time and I need to separate this into 2 separate cells, 1 for the date and 1 for the time. Sample is attached for the before and after formats.

View Replies!   View Related
Negative & Positive Results In Separate Cells
Two columns contain data (cash amount) which should be the same. If there is a difference, I need to show a minus value in a different cell and a plus value in another cell.

View Replies!   View Related
Format Random Number Cells As Draw Result Entered
Format Random Number Cells As Draw Result Entered ...

View Replies!   View Related
Formula To Clear Out Cells With In A Spread Sheet If Certain Input Entered.
On the attached spread sheet here is what I am trying to do. In B2 I will either enter D (digital label) or F (Flexo label). With that than if a D is entered nothing else will happen with in spread sheet but if F is entered D23:Q40 will need to be cleared out and blank.

View Replies!   View Related
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...

View Replies!   View Related
Separate Space$ Delimited String Into Multiple Cells
I am trying to split a string into separate cells. I have managed to generate the formula for the description and first dlr value in the string but I am have trouble figuring out how to build functions for the rest of the string.

Example attached.

View Replies!   View Related
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.

View Replies!   View Related
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

View Replies!   View Related
How Can I Check In Cell C2 That Only Let A Value To Be Entered If G2 Is 10 And F2 Is 20
How can I check in cell C2 that only let a value to be entered if G2 is 10 and F2 is 20.

I can do the one but not both together

View Replies!   View Related
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 ?

View Replies!   View Related
Value Entered In A Cell Based On A Date In Another Cell.
I need a formula that will basically just display a 1 or 0 in one cell based on the date in the cell next to it.

If the date is older than 180 days then a 0 will be displayed in the cell.

If the date is less than 180 days then a 1 will be displayed in the cell.

I have an attachement as an example.

View Replies!   View Related
Getting Value To Change When Value Entered In New Cell Of Column
Not sure if I can do this with a formula or not. looking to change the value of a cell when using a column. Im looking to have the cell to change each time after there is a new value entered in the column but only when a value is entered in new cell of the column.

View Replies!   View Related
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
Option Explicit

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)
Else
Set Rng1 = Union(Range(Target.Address), Rng1)
End If
For Each Cell In Rng1

View Replies!   View Related
Prevent A Character From Been Entered Into A Cell
Is there any neat little way of preventing a character say '=' from been entered in a cell, kinda similar to the validation but for a 'dont allow' function.

View Replies!   View Related
Show Message Box When Certain Value Entered Into Cell
This seems like a very simple question but I can't find the answer that I'm looking for. I have a spreadsheet with a drop-down ( validation) list. Based on what the person selects, I then would like to have a message box to come that would direct them that certain cells then need to be filled in.

View Replies!   View Related
Changing The Cell Color With A Certain Value Is Entered
I would like the cell to be red if the value is below 1.5. If the value is 1.5 or greater, I would like for that cell to turn green.

View Replies!   View Related
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

View Replies!   View Related
Check If Time Has Been Or Is Entered In A Cell
Is there a simple function to check if a time has been or is entered in a cell, just like the IsDate function.

View Replies!   View Related
How Do I Put 2 Separate Sums Into 1 Cell?
I have a listing of numbers in different catagories such as the following:

Type A:
1

Type B:
2


Type C:
4

Total:
7

I want to be able to have a cell automatically calculate to say:
Type A:
"(1/7)"

Type B:
"(2/7)"

Type C:
"(4/7)"

with that exact formating.

View Replies!   View Related
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.

View Replies!   View Related
Separate Information From Cell
how do i separate information like this:-

01/08/2007,09:54:27,27.05,27.06,27.05,361300

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


View Replies!   View Related
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.

View Replies!   View Related
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.


View Replies!   View Related
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.

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved