Split Names From 1 Cell To Many

Dec 16, 2007

Is it possible to copy specific names based on cell content?

For example:
If I have a cell containing text and the names Bob, Mary, Bill,

I would like have a macro read the contents of the cell, find each name "Bob", "Mary", "Bill", then write their names in the first available cells to the right.

ie: If Cell A1 contains Bob, Mary, Bill
write the names in the next available cells ( in any order)

Cell B1 = Bob
Cell C1 = Mary
Call D1 = Bill

It would be great if this can be done within a range..

If anywhere A1:A100 contains the names Bob, Mary, Bill,
to write the names Bob, Mary, Bill ,in their next available cells.

Also, I have a few dozen names that i'll be incorporating in my worksheet which I'm hoping to expand on.

View 9 Replies


ADVERTISEMENT

Split 1st & Last Names & Split Addresses After 1st Comma

Mar 6, 2008

I'm using Excel 2000 and I have a spreadsheet with 4 columns (A-D) and many (500+) rows.

Part 1:
#########################################
Colums A & B both contain identical data - a first name and a last name in the format "John Doe".

I want the second word ("Doe") removed from all cells in Column A so that only the first name remains, and I want the first word ("John") to be removed from every cell in Column B so that only the last name remains.

So, where A1 & B1 both started with the data "John Doe" now A1 contains only "John" and B1 contains only "Doe".
#########################################

Part 2:
####################################################
Column C contains addresses in the format:
"#5 - 123 Fake Street, Some City, CA 90210"

There is ALWAYS a comma and a space after the street address, then the name of the city or town followed by more data which may include one or more commas.

I would like everything BEFORE the first comma to remain in column C, and everything AFTER the first comma & space to be moved into Column D of the same row. The first comma and space are not needed again.

So, where C1 started with "#5 - 123 Fake Street, Some City, CA 90210", it now only contains "#5 - 123 Fake Street" and D1 now contains "Some City, CA 90210".
####################################################

View 6 Replies View Related

Split Names Using Macro

Nov 23, 2007

I have file that may contain about 100 or more rows depending on the request. In column A, i have several names that is seperated by comma in ever row so basically what i want to do is to remove each name and insert a new column and paste the removed name in the new column and keep doing that until only one name is left for each row. I would like the code to check every row until the last row of the file and perform the splitting method that i have mentioned above. Pls. note that some row may contain only one name in this case we don't need to copy and insert new column but if the row contains more that one name then i would like to split it as shown below. I have attached file for details. I really appreciate for your help.

Here is an example of the current issue
Mike, David, John
Eric, Dave
Joe, Johnson, Edward, Ellen
Thompson, Adrienne.........

View 14 Replies View Related

Split Names In List

Sep 8, 2012

In column A of my worksheet I have a list of names (aprox 300) that are shown in the following example..

John Smith
Aron Jones
Sue Hisplof
etc etc

Is there a formula that will place each of the Surnames only in the adjacent column B...so from above list column B would display...

Smith
Jones
Hisplof

View 4 Replies View Related

Split Multiple Names

Aug 1, 2007

I have and issue where I have name in one cell and need to separate them into 2 cells. Trouble is it is a combination of single names, husband and wife, and partners with different surnames. Sample of data is below:

Belinda Smith & Grant Jones
Janine
Ken & Marie Smith
Louie & Betty Johnson
Naomi Thomsoon & Craig Brown
Narele & Barry Day
Nicholas & Julie Smithson
S M Bole & Co
Jim Morris
Danielle & JohnSimpson

View 9 Replies View Related

Find Formula (find Jobs With Split Names)

Aug 5, 2009

DSee the attached.

The current spreadsheets add up each persons totals by matching the name in each tab with the name of the person who won the job located in current orders tab.

BUT.....If two salesman pair up on up on a job then the formula doesn't recognise the joint name. eg Gary/paul in row 69 (current orders).

I need the totals to half the job and add it to the salesmans total accordingly. There is no 'Paul Tab' as he is our MD and doesnt have a target.

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

Split Data In A Cell And Get The Value For The Split Data From A Different Table

Jul 31, 2006

Here's my problem. I have a cell where there are many data strings seperated by ",". Each data string has a seperate value of its own like for e.g:
A2: aa,ab,ac

String Value
aa 1
ab 1
ac 3

What I want it accomplish is that, split the A2 cell into the different data string entities seperated by ",", then get the corresponding value of each of the data string entity, and to take the average of all the values of the different data string entities.

View 9 Replies View Related

Split Numbers And Split Words

May 8, 2009

How do I split numbers!

I have two problems/challenges!

Part I...
I got the answer 1987, and now I want Excel to take the numbers out and display...
1 in one box then i set + in the next, then 9 in the 3ed. box, next box +, then 8, then +, then 7 in the last so that i can have Excel make a SUM of it all to 25.

How do I split 1987 and put the numbers in different boxes?

Part II...
I want to make A=1 B=2... all the way up to 9, then start over again with J=1 K=2... up to 9 again and then over again.

So that if I write my name it comes out as a value of 14 (Odd = O=6 D=4 D=4 =14)

(AJSØ=1 BKTÅ=2 CLU=3 DMV=4 ENW=5 FOX=6 GPY=7 HQZ=8 IRÆ=9, It's the Norwegian alphabet, that's why there are some extra letters)

So how do I set up my Excel so that is ANY name is typed in I can get it out into a number from the values assign?

View 14 Replies View Related

Cell Getting Split Up

Feb 3, 2014

I have a very long HTML code that i am using for a description of something on our website, when i save the file in CSV format the HTML gets cut up and put into different cells on different lines ... the file needs to be saved in CSV format.

View 1 Replies View Related

Split Cell Into Two

Dec 27, 2011

I have a list of fixtures but each cell lists the teams in each game ... i.e.

CSKA Moscow v Real Madrid (in one cell)

List example....

CSKA Moscow v Real Madrid (Cell 1)
Leicester v Portsmouth
Leyton Orient v Charlton
Liverpool v Newcastle
Lyon v Apoel Nicosia
Man City v Liverpool

I want to split these cells so CSKA Moscow is in cell B1 and.... Real Madrid is in cell C1 etc.

View 5 Replies View Related

Split Cell Into 2

Sep 17, 2009

I have a column with info like

name - name

as in (column a): frank - john

I want to split that into 2 cells(columns) and drop the -, what is the best way ?

so now column b would have frank and c would have john.

View 9 Replies View Related

Split Up Contents Of Cell?

May 2, 2014

If Cell A1 Contained 1,154,546,487,47,36,48,47,668 and so on. Using a Formula or Macro how can I get it to take each number(s) before each comma and put it in the next column on the same row?

So using the above example numbers you would end up with 1 in column A1 and 154 in column B2 and so on and so forth.

View 8 Replies View Related

Split One Cell Value Into Rows?

May 8, 2014

I have got one column in which each cell has the complete data for the entire row.

I would like to split the data in one cell into the same row.

Got about 4000 records like it.

View 1 Replies View Related

Split Cell Format

Jun 11, 2007

I am looking for a method of combining 2 pieces of data into 1 cell

ie 2 Cells merged? and/or formatted? as one split cell as per the mock-up in the attached word document.

120 Enquiries, Closing Ratio of 40%

to look something like the attached

View 10 Replies View Related

Split Data From One Cell...

Mar 9, 2009

I'm working on a datasheet that needs some tweaks...

In column A, I have these data
CA 902-20
GA 100-10
UP 200-20
WC 100-20
UP10-299
Best99-000
City #123-89

how can I tidy the data into column B so that they have it will have a standard format of a single letter followed by (no space) 5 digits.
ex: C90220
G10010
U20020
W10020
U10299
B99000
C12389

I am thinking using Left and Right formula, does that sound right?

View 3 Replies View Related

Split Data Within A Cell?

May 29, 2009

My data within a cell has 3 values separated by commas i.e. abc, def, ghi. I want to separate these values into their own cells. Is there a formula or command that can do this? of course I can go into each cell and cut and paste the values onto another cell, but I have thousands of cells that need to be reformatted.

View 3 Replies View Related

Possible To Split Cell Diagonally?

Apr 3, 2013

I've searched on Google if it was possible to split a cell diagonally, so that I can enter a title on each side of it. One of the answers was from this forum but is very old, the thread was posted in 2005, and the replies are all turnarounds, like drawing manually a line, or such solutions. So I was wondering, maybe with all the new Microsoft Excel updates and upgrades, maybe there's a simpler more straightforward way to do it?

View 2 Replies View Related

Split Contents Of A Cell

Jan 30, 2009

i'm trying to do is split the contents of the cells in column A in to 2 seperate cells. The contents of the cells in column A are like this: alpha.bravo.charlie. I want to delete alpha and have bravo as the new contents of column A and charlie as the contents of column C.

View 2 Replies View Related

Split String In A Cell

Nov 13, 2009

I need to split a string in all cells in one column (delimiter = ""). What I basically want is to get just the first word in the cell.

View 4 Replies View Related

Split Text In A Cell?

Apr 17, 2013

I have grid references in a list I.E. TQ1234597865. i need to split that into 3 separate cells, so I end up with TQ, 12345 and 97865 in separate cells. Is there an easy way to do this with formula ?

View 2 Replies View Related

Split A Cell In Half

Jul 29, 2008

Is there any way I can split a cell in half, so I can put numbers in the left part of the cell, and the right part?

If so, I would I refer to those numbers in my macro?

View 9 Replies View Related

Split Cell Contents

Sep 18, 2008

The columns A & B need to be split as shown in the table (3 columns as shown in D,E & F)
In column A the hrs needs removing, but there is also some spaces at the front of this text which needs removing.
Column B needs splitting as shown in the table.

******** ******************** ************************************************************************>Microsoft Excel - MAPPED SCHEDULE.xls___Running: 11.0 : OS = (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCDEF1 4.3 hrs1-08-739677-00 U1 DC STATOR COOLANT PMP 4.301-08-739677-00U1 DC STATOR COOLANT PMP2 1.1 hrs1-08-739543-00 SW/BD ALARMS FAILED 1.101-08-739543-00SW/BD ALARMS FAILED3 1.1 hrs1-08-739544-00 ALARMS FAILED ON TEST 1.101-08-739544-00ALARMS FAILED ON TESTSheet2 [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

View 9 Replies View Related

Split Cell With 2 Different Delimiters

May 1, 2008

I'm trying to split a cell with 2 different delimiters.
As example

A1 10'48"
to
B1 10
C1 48

I have about 10.000 entries like those. And i think a macro should be the most suitable for it.

But I'm not experienced enough to make it work.

View 3 Replies View Related

Split Number And Words From One Cell To Other

Jun 1, 2014

Cell contains numbers & words. So how can we split numbers to 1 cell & words to other cell.

View 2 Replies View Related

Split Cell W/o Text-2-Column

Apr 7, 2008

I have a cell that has this in it:

"5+5"

That's how the data arrives to me. I would like to have another cell display the result (i.e. 10) so I won't need to calculate everything by hand. How can I do this with a formula? I don't want to use text-to-columns, and I'd prefer not to use VBA.

View 13 Replies View Related

Split Data From 1 Cell Into 2 Cells

Jun 17, 2009

I have a list of first names and surnames, the first name and surname are in one cell together but seperated by a commer.

For example in A1 would be Steve, Jones

What I want to do is have this split into two new cells. So in A2 I would have his first name and in A3 his surname. Formula to do this is what I cant get to work.

View 2 Replies View Related

Split Single Cell Into 2 Or More Cells

Apr 24, 2012

I would like to know if you can split 1 single cell into multiple cells in 1 column?

View 11 Replies View Related

Split Cell Reference In Half

Jan 8, 2013

I have two sheets. One containing in cell B2 "1211 - Extra Core Hours" (t_Cost_Centre_data) and then another sheet that displays the data. On the display sheet, there need to be two fields. Event ID and Event Name. The event ID is the "1211" and the name "Extra Core Hours".

My question is, how do I split the reference so that only the ID and name appear in the relevant cell?

Ie Event ID Event Name
1211 Extra Core Hours

Note, the ID's and names will change and they will be multiple.

Formula Help!.xlsx

View 3 Replies View Related

Split Cell Data Into Rows?

Feb 5, 2014

I have data in column A and Column E & F shows the required output.

View 7 Replies View Related







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