Separate First And Last Names Entered In One Cell Into Two Different Cells

Feb 4, 2009

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 6 Replies


ADVERTISEMENT

Separate The First And Lasts Names From One Cell Into Two Cells

May 18, 2008

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

Parsing Names In Separate Cells

Oct 23, 2009

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

Pasting List Of Names Into Separate Cells

Feb 14, 2013

I have a list of names in a Word doc that is set up like this:

John Doe
Jane Doe
Doug Birch
Mary Birch

Is there a way to copy that list and paste it into Excel and make each name go into its own cell.

Example:
A1 John doe
A2 Jane Doe
A3 Doug Birch
A4 Mary Birch

View 5 Replies View Related

Separate Names From Dates & Commas In Cell

Mar 26, 2008

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

Shade A Cell Red If Two Separate Conditions Apply (in Separate Cells)

Dec 3, 2013

I'm trying to use a formula in conditional formatting to highlight a cell red if the cell contains a 0 but the date shown in another cell has passed. I want to copy the formatting throughout a column but I don't want the cell to highlight if there is no date in the other cell concerned.

View 11 Replies View Related

Formula To Erase Names From One List When Entered Into Another?

Jul 8, 2014

I have a list of names in separate but connecting cells (list 1). I would like to use list 1 as a population from which to select and enter names into other cells (list 2). Is there a way that the names in list 1 would disappear from the available population when entered into list 2?

View 3 Replies View Related

Locking Cells Depending On What Is Entered In Another Cell

Sep 22, 2009

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

Excel 2010 :: Lock Out Cells If A Value Is Entered In Another Cell

Mar 14, 2012

I am trying to lock out cells if a value is entered in another cell in Excel 2010. For instance, when entering a value into N7 (merged Columns N7,O7,P7 into one - not sure if that matters) I want Q7 (same thing, merged Q7,R7,S7 into one) and T7 (merged T7,U7,V7 into one) to be locked out.

So, if N7 has anything typed into it, I would like Q7 and T7 (merged) to be locked.

If that's possible, I would also like for it to happen from rows N7 - N21 ...

Is this hard to do? My understanding is that it's only possible through VBA ... am I accessing the script correctly? (right click the 'sheet 1' tab [named Issues log], select 'view code').

View 9 Replies View Related

Splitting Names Into 2 Separate Columns

Apr 11, 2008

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

Separate Column Of Names Into 2 Columns Of First And Last Name?

Nov 13, 2013

I have a column of first and last names that sometimes look like this: Smith, Michael D and other times look like this Smith, Michael (no middle initial) I would like to separate them into two separate columns, one for First name and the other for last name. I have no use for the middle initial so that can be ignored.

View 4 Replies View Related

Highlighted Cells (enter A Character And For Each Cell Within The Selection That Value Is Entered)

May 8, 2009

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

Formula Needed To Separate Names From Numbers

Sep 21, 2012

I have a phone list set up as LastName, FirstName PhNum all in a single column. The problem is, some last names have a space, some phone numbers are in the xxx-xxx-xxxx format while others are just an extension like xxxxx.

Example:
Smith, John 519-123-4567
Van Smit, Joe 12345

What can I use in a formula to extract the numbers so that I can end up with 2 columns, one with names, the other with numbers.

View 6 Replies View Related

Separate Text From One Cell Into Other Cells?

Feb 21, 2014

Is it possible to that the contents of a single cell and and place text of that cell into separate cells? From a web output I have some like the following in my first cell

asdfsdf|adkjoi|fdf234sd|fsf3ie43|||asdfjlkei393|dfjvie|d||adfjei|...goes on with 28 "|"

The text is in A1 and I would like it to take the first string of characters up to the | and place it into cell A2

Then take the next set of characters up to the next | and place into cell A3 and so on.

Once the cell in A1 has been completely separated, go down to the next row and do the same until it reaches the end of the data.

Normally I would create a text file with the web data and then import that in with "|" delimited fields but I don't want to create that extra step for the user.

View 7 Replies View Related

How To Separate Text From Numbers Into Two Separate Cells

Feb 13, 2014

I'm trying to separate text from numbers into two separate cells...

Essentially, I would like the users to copy and paste data into Column A, as seen below. Then, hopefully by formula separate the text characters into Column B and the numbers into Column C.

Input: Output 1: Output 2:

Col A Col B Col C
Wells 123 Wells 123
Wells 1234 Wells 1234
Wells Fargo 123 Wells Fargo 123
Wells Fargo 1234 Wells Fargo 1234
Wells Fargo Inc 123 Wells Fargo Inc 123
Wells Fargo Inc 1234 Wells Fargo Inc 1234

Ideally, I would like to do this with a formula...

View 6 Replies View Related

Lookup Names Within Text And Return That Name To Separate Column If Found

Jun 13, 2013

I'm using a formula to lookup names within text and return that name to a separate column if it's found.

The formula is =INDEX($E$1024:$E$1026,MATCH(FALSE,ISERROR(FIND($E$1024:$E$1026,E1010)),0))

I understand index and match functions, but I'm confused regarding the use of FALSE as the lookup value and the iserror(find( usage.

View 2 Replies View Related

Formula That Adds Two Separate Cells If Third Cell Has Certain Value Into Another Cell

Aug 5, 2014

I'm working on a spreadsheet that includes items I sell as well as the packaging it goes in.

The formula I'm looking for basically will tell a cell that if one cell (# of box) is equal a certain # it will add two separate cells by the weight of the box.

I have cells for each of the following: Weight in lb and weight in oz of the item (no box), a cell which holds the number of the box (ranging from 1-15) and two cells with weight in lb and weight in oz which would be the total weight of the item + box.

So, for example, if weight of item equals 1 and box cell equals 2 then it will put the weight of item + weight of box into a third cell.

Here's a picture of my current spreadsheet : [URL] .....

Basically, I would like to do something such as if P3 = 5, then Q3 will equal N3 + U6 and R3 will equal O3 + V6.

View 12 Replies View Related

Using Data In Two Cells To ID Separate Sheet And Cell

Oct 23, 2009

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

Separate Comma Separated Cell Into Many Cells

Mar 15, 2008

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

Separate/Split Data In One Cell To X Cells

Jun 3, 2008

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

Separate Data From One Cell To Multiple Cells On Different Sheet

Sep 10, 2012

I have a sheet labled Parsed and a sheet labled Prices. The Data on the Parsed sheet is in W2:W1000. the data on the Parsed sheet, can have anywhere from one value to up to ten values in it. (again all sparated with a space.)
the data is separated by a space between each number value. I am trying to find a way to take the values from Parsed!W6 for example and separate the data into the prices sheet in to different columns. Some cells may be blank.

Example:

PARSED SHEET.

W
X

1
Equipment

[Code] .......

View 2 Replies View Related

Cell Addresses Derived From Concatenated Values In Separate Cells

Apr 2, 2013

I need to return a value from a cell in Column H, but with a row number that varies with each entry.

I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:

=Display Value from in column H: (row number per cell B1)

And cell B1 would include the number 3789.

So the returned result is the value from cell H:3789

View 4 Replies View Related

For A Column Of Cells Separate Text In Single Cell Across Multiple Columns

Nov 29, 2013

New task for work today, which involves creating a spreadsheet for all existing members of an organization. I went to the organization's website and copied all of the names & info, then pasted into a blank spreadsheet. All of the entries were separated into their own rows, but all of the information is only in one column. Take a look at my sample:

(NOTE: does not contain actual names or info) Book1.xlsx

This sample only contains 5 entries, but my actual list contains about 200; if it had just been the 5, I would have been fine with manually separating the information, but for 200 entries I need something much faster. You'll notice in the sample that the company, person, address, phone number, email, and website (when there) are not separated by anything. I know that using Text-to-Columns, I would technically be able to achieve what I need using the Delimited option, but I can't imagine this working without separators. I thought perhaps there might be a way to separate them based off font changes or something? Or maybe some way that I can insert a semi-colon or some kind of separator between the necessary data?

While the font is Arial for the majority of the entries, in terms of font changes: company font size is 12 and color is navy blue; the person's name is size 18, bolded, and dark grey; the address & phone number are size 9 and the color is light gray; and finally, the email & website are also size 9, but navy blue in color. However, you'll notice that two of the five entries are formatted differently. This is because formerly, the entire cell & its text were a hyperlink to the email. I went ahead and removed these hyperlinks, simply using a "Remove Hyperlink" macro walk through I found on the web, but now these cells are uniformly set to Calibri and size 11, my default font setting.

View 6 Replies View Related

Combining Two Values In Two Separate Cells To Make A Cell Reference Or Index Refer.

Jul 15, 2009

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

Shading And Locking Multiple Cells From Input Based On Value In Separate Single Cell

Sep 23, 2013

Now the problem, Each client has a sheet in a workbook. In each sheet, I input the number of days of service available to that particular client in a single cell. We'll say cell [G8] is the cell used for this input in all sheets. If the client has 30 days of service available then we type [30] in cell [G8]. Each sheet is a 100 day template. I want to shade in dark grey and lock from input the days NOT available to the client based on the days of service input in cell [G8]. If 30 days of service are available in cell [G8] then the remaining 70 days on the 100 day template would be shaded dark grey and locked from input. That is, day 31 through day 100 of the template would be locked and shaded. Each individual day has multiple cells in a column. I want to lock and shade each column of cells in each day not available.

View 5 Replies View Related

Linking Cells Globally To Allow Users Ability To Change Cells On Separate Sheet / Cells?

Feb 18, 2014

I have a workbook that uses the values that a user had entered into 3 cells to calculate multiple other charts/diagrams on multiple sheets within the workbook. Each sheet would show what the user had entered in the 3 cells to allow them to see what is being used to calculate each table. Is it possible to link these cells so that the user can change the 3 values without having to go back to where he originally entered the 3 values?

For example, a user has entered in 3 values in Sheet 1. A formula in Sheet 2 displays what is entered by the user and uses these calls in Sheet 2 for calculations. When the user wants to change the three values, he would have to navigate to Sheet 1 and enter in the new values to have the workbook recalculate all the tables. Is there a way to link the three cells from Sheet 1 and Sheet 2 so when the user is on Sheet 2, he has the opportunity to change the values on the current Sheet without having to navigate to Sheet 1 to do so?

View 1 Replies View Related

Table Names Revert To Cell Names When File Is Reopened

Jan 4, 2013

I have a worksheet with many tables that I use in formulas.

I like tables for a couple reasons, one being the ability to insert/delete rows without affecting the rest of that worksheet row.

Also, automatic copy of formulas/formatting is great.

But, what I really like about tables is the ability to use the naming conventions in formulas.

Problem is when I save this worksheet, after I close it and open it back up, all table references in my formulas have been converted to cell references.

Example:

Code:

=IF((SUMIF('Quote 1'!$M$28:$M$43,">"&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&'Quote 1'!$J$57:$J$60)*'Quote 1'!$G$57:$G$60)+(COUNTIF('Quote 1'!$M$28:$M$43,""&tblOSSRV[Min Order Cost])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],""&tblOSSRV[Min Parts Per Line])*tblOSSRV[Cost / Part])+(COUNTIF(tblFam[[#Data],[Qty by Factor2]],"

View 4 Replies View Related

Match Names In Two Separate Column If Equal Then Pull Info In 3 Column?

Dec 6, 2013

I need to look up the name in E2 in the list in column A and if it matches then lookup name in F2 in list column b, if it matches then the corresponding number in column C is displayed in column G. If neither names are in the 2 columns the words"Not on lists" is displayed in cell in column G.

The other problem is one name is spelled two different ways I want it to look for both spelling before moving on to looking up the second name.

I started with this formula but I'm getting #N/A or "not on list" when they are on the list. I'm using ranges prod_sum is columns AthruC, Last_name is range BthruC. =IF(AND(VLOOKUP(F4,prod_sum,3,0),VLOOKUP(I4,last_name,2,0)),"not on list")

View 6 Replies View Related

How To Separate Row Data From One Workbook Into Separate Workbooks Based On Cell Data

Apr 23, 2014

I need to separate row data from one workbook into separate workbooks based on cell data. Currently I filter the data, copy and paste it to a new workbook and save it. It's a tedious process and was hoping to find a way to automate it. I have attached a sample file. In this instance, I would like a separate workbook for the filtered data in Column 1 and then all the row data gets copied to new workbook. So all of Pennsylvania data would get copied to new workbook, then all of the Michigan data gets copied to a new workbook. I have also attached an example of the end result that I need.

View 9 Replies View Related

Check All Cells Are Entered

Jul 13, 2009

I am using the followng code to check data is entered in a cell ...

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved