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
ADVERTISEMENT
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
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
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
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
View Related
Dec 10, 2008
I need to make this macro read FOLDER names instead of FILE names. When I posted this question yesterday to get this macro, I wasn't told that each file in its own folder. I need the folder names now.
_____________________________________
Sub test()
With Application.FileSearch
.NewSearch
.LookIn = "C:Ford"
.SearchSubFolders = False
.Filename = "*.*"
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Cells(i, 1) = .FoundFiles(i)
Next i
Else
Cells(i, 1) = "No files Found"
End If
End With
End Sub
View 9 Replies
View Related
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
Jul 10, 2012
I'm using a CALL Macro to split up a HUGE macro into different pieces:
Code:
Sub RSLDASHBOARDV2()
'Macro recorded 12/14/2010 by Ryan R. Koleno, Pharm.D.
'Last Updated 7/10/12 by Ryan R. Koleno, Pharm.D.
'Do Not Modify Code Unless Given Proper Privileges to do so.
Dim APPSPD As Worksheet
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
[code]...
The first few macros dealing with page setup and what not work fine but when it hits the Pivot table code for the STATSPIVOT macro it errors out stating: "Run-time error '1004': Unable to get the PivotItems property of PivotField class' at this point in the code:
Code:
objField2.PivotItems( _
"TRC").Position = 1
objField.PivotItems( _
"MEDCO MAIL OR AOB").Position = 2
When this macro is not split up it worked fine as written. Am I overlooking something in the Call Macro's code or is there a variable I'm not aware of. I have included the Pivot Table code that errors out as well.
Code:
Sub STATSPIVOT()
'STATS PAGE BASED ON STATS DATA TAB
Sheets("STATS DATA").Select
Dim objTable As PivotTable, objField As PivotField
ActiveWorkbook.Sheets("STATS DATA").Select
Range("A1").Select
[code]...
View 4 Replies
View Related
Jan 15, 2014
I have a set of numbers (representing time), and I need a macro that will generate a split of these numbers that both have an equal sum (or as close to equal as possible). Also, everytime the macro is run, a different split should be created.
View 8 Replies
View Related
Dec 1, 2009
I have a excel sheet that contains about 8000 entries... I need to create a macro to help me. my file is like this:
123
RT4
eliott.billy
mark.garth
elena.david
I would like to split first name and last name. All the ligne that contains "." split it in 2. Example: eliott.billy will become eliott (column A) billy (column B)
View 5 Replies
View Related
Oct 5, 2011
I need to split a worksheet into new workshets in the same workbook using customer names in column D which includes subtotals. However, the macro i am using splits the customer name and customer subtotal into different sheets as the customer name now contains "total" so the macro doesn't recognize this is the same customer.
E.G
Customer name
Kwik Fit
Kwit Fit Subtotal
Mail Marketing
Mail Marketing Subtotal
I need the macro to split the above customer names so that the customer name including the subtotal gets split into a new worksheet AND the new worksheet is named after the customer name.
I am aware their are add ins for such tasks but i am using this at work and cannot install any files.
Code:
Sub Lapta()
Dim lastrow As Long, LastCol As Integer, i As Long, iStart As Long, iEnd As Long
Dim ws As Worksheet
[Code]......
View 1 Replies
View Related
Dec 2, 2013
I want to split numbers in a cell. At the end of my report there is a text like this ;
Critical Path : 35-36-37-38-39-40-32-9-10-11-12-13-14-15 ;
Total Pressure Loss : 217.1
Pa text row changes in every report but it is always in a1 column is there any way to search critical path and split numbers in text.
My result should be : -35-36-37-38-39-40-32-9-10-11-12-13-14-15-
View 3 Replies
View Related
Apr 12, 2014
I usually copy ready macro from google. And I currently working on a check printing excel for my company to print check. I have googled and extract this macro from an author and place into my excel. But i run into a problem which is, the returned text will exceed the boundaries,
is there a way to spilt the text, lets say from after "dollars " to another appointed cell?
[Code].....
View 5 Replies
View Related
Apr 18, 2014
This is my input:
Col A
asde,dsfts-qwsa/001,asdasd-qwsa,asdas
etert,ttyutt-tww/013,dgdd-tww,dsfsaa
.
.
.
.
.
Col B
qwsa
tww
In Col C.. I want the output to be as
asde,asdas
etert,dsfsaa
Requirement as a text:
I need a macro that removes the value in column A based on the value in Col B.
The data that the macro removes is always between the "," (comma).
View 6 Replies
View Related
Jul 7, 2009
I have a large workbook with many worksheets which are all grouped into pairs - i.e. Sheet1 & Sheet2 go together, Sheet3 & Sheet4 go together; Sheet5 & Sheet6 go together, etc. etc.
I need a macro to divide this workbook into separate workbooks where each group of worksheets has a separate file of its own and I want to name the new workbooks after the second sheet in each group:
i.e. A new workbook for Sheet1 & Sheet2 called Sheet2.xls; a new workbook for Sheet3 & Sheet4 called Sheet4.xls; a new workbook for Sheet5 & Sheet6 called Sheet6.xls; etc. etc. etc.
View 12 Replies
View Related
Jul 15, 2009
I have a huge data which needs to be divided and distributed to team members. What i want to achieve is to split, thru macro/VB, the entries in my main file into several files, say by 50s. I attached files for reference. Whereas, the Dummy.xls is the main file and Book1 & Book2 are the desired output.
View 3 Replies
View Related
Oct 30, 2010
I have a macro below which splits the data in my workbook (Attached) into seperate workbooks on the basis of each change in data, and saves the new workbooks with the value available in column A. Everything else works perfectly with this code I just want the code to take the workbook name from Column B, not Column A as it is currently taking.
Sub Test()
Dim Sh As Worksheet
Dim Rng As Range
Dim c As Range
Dim List As New Collection
Dim Item As Variant
[Code] ......
View 9 Replies
View Related
May 16, 2011
I have a file, with this dummy data
Fruit Country QTy
Apple India 100
Apple USA 200
Orange India 150
Grapes Aus 100
Grapes India 100
I need to macro, wherby it will create 3 files namely apple, orange,grapes and store the relevant rows.
Hence I need a macro to split file into different files based on different values in a column and accordingly name it .
View 9 Replies
View Related
May 3, 2012
I have Column D containing multiple values that I need to split. The structure is:
Text Text (XYZ) 123 AB C
Text 456 C
Text Text (RST) 7 A CD EF
Text 22
I'd like is to split the cell using a macro rather than formulae so that the resulting cells can be filtered.
The output would be:
Text Text (XYZ)|123|AB|C
Text|456|C
Text Text (RST)|7|A|CD|EF
Text|22|
So the first split would need to be when the number is reached, the second split after the number and then subsequent splits each time a space is encountered (although there may not be any values after the number!).
View 6 Replies
View Related
Aug 5, 2013
My code below returns a type mismatch? It is looking at the values in column B which are formatted as text and the output is in column J. An example of a value is 2.1.15 I want to extract 1 (i.e. the central character between two ".").
VB:
Sub ConvertLineNo()
Dim r As Long, TempStr As String
For r = Cells(Rows.Count, "B").End(xlUp).Row To 1 Step -2
TempStr = Cells(r, "B")
TempStr = Split(TempStr, ".")
Cells(r, "J").Value = TempStr
Next
End Sub
View 3 Replies
View Related
May 2, 2014
I just want a macro to split the hours to a date and time in column A. I just preferred macro rather than a formula.
Ex: [Code] .......
Column B1 should be: [Code] ........
I've also attached the file : hours split.xlsm
View 5 Replies
View Related
Nov 1, 2007
I need a macro that can search text cells in column L for a space delimiter, then if the space is present, to split that cell into a row inserted below it, then copy the information in the rest of the row down. I'm not really up on my VBA.
View 13 Replies
View Related
Apr 16, 2014
Excel VBA 2010.
I have this problem:
e.g Cells ""
C2 - 128.50 g
C3 - 211.01 g
C4 - 198.50 g
C5 - 179.34 g
I need to split the text into
Cell "C2" = 128.50 and Cell "D2" = g
Cell "C3" = 211.01 and Cell "D2" = g
and so forth
I wanted to avoid using delimited method with space and induce an automated method to format every cell of Column "C" one by one.
View 6 Replies
View Related
Jun 19, 2014
I am trying to divide a table of data (7 Columns, 500-1000'ish rows) into individual worksheets based on any unique values in column. There may be anywhere from 10-30 instances of the same value in Column C, and I need to bring all rows of each unique instance into a newly created worksheet to be labelled the same as the value in Column C.
The RAW data will be updated monthly by keeping a template file copying and pasting data, therefore I am hoping to have a "SORT" button with this Macro assigned to it. I have quickly thrown together a sample workbook with how the RAW DATA will be pasted into the file, with additional sheets showing how I would like the data split and organised.
View 2 Replies
View Related
Mar 30, 2013
Example: Column A has a mixture of letters and numbers. ie AU1234 or AU5678 Always the letters will be first, but not sure if 2 or 3 letters. Need to insert space between letters and numbers.
I have so far. " =(left(a2,2)) & " " & (mid(a2,3,(len(a2)-2))) " this works if all are only 2 letters...
Now. What I need to do is open a .csv (will do manually) then hit something like ctrl-alt-k to run macro.
Step 1: Insert a column next to A, check rows down and for however many rows, make above formula (include 2 or 3 letters) to insert space between letters and numbers, select the new column, copy, select column a and overwrite with the values from the new column. ie turn 'A2' from "AU1234" to "AU 1234" and 'A3' from "AU4567" to "AU 4567" .
Step 2: Column D has comma delimited fields. Column F also has comma delimited fields. both D and F will always have the same number of fields. D will be something like 1234,2345,3456 ------ in this case 3 fields but could be over 100 fields
F will be something like M0002456 (04P), M0002457 (05P), M1230477 (02A).
Need to split both D and G from row A2 simultaneously from comma fields to rows. copying all other data from row. and insert before the next set of data in what was previously A3 and (in this case *should* be moved down to A5 because of the 2 inserted lines from the 2 extra fields).
E.g.: Column A Row 2 "AU 1234" Column B Row 2 "data1" Column C Row 2 "data2" Column D Row2 "1234" Column E Row 2 "data3" Column F Row 2 "M0002456 (04P)"
Column A Row 3 "AU 1234" Column B Row 3 "data1" Column C Row 3 "data2" Column D Row 3 "2345" Column E Row 3 "data3" Column F Row 3 "M0002457 (05P)"
Column A Row 4 "AU 1234" Column B Row 4 "data1" Column C Row 4 "data2" Column D Row 4 "3456" Column E Row 4 "data3" Column F Row 4 "M1230477 (02A)"
Then carry on to next row which may have only one field and can be ignored/skipped to the next which may have 100 fields which will need to be split to rows and inserted...etc....
Step 3
Remove all the "space Bracket-data-Bracket" ie " (04P) from column F
View 7 Replies
View Related
Jul 30, 2013
I'm pasting a lot of data into a spreadsheet and then using a macro to split the data into three different sheets/tabs. I'm doing this by simply copying entire columns. Each sheet/tab has a headings row and autofilters added so that my boss can filter on certain manufacturers in one of the columns.
The problem is that when he selects a manufacturer, at the bottom left corner of the screen it shows the number of records, which at the moment will be something like "6 of 65211".
The actual sheet only has a few hundred rows (not 65211). So how do I delete all the blank rows beneath my data using a macro or vba code?
As an extra point, I'd like to use column B to check for blank rows (not column A).
View 6 Replies
View Related
Jan 10, 2014
I need to write a macro to split an excel file into separate workbooks and automatically email our project managers.
View 1 Replies
View Related
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
Feb 28, 2007
i am having trouble with a macro of sorts that will allow me to see the names (B5) Create them it as a title in "sheet2" and reference the corresponding values into a table form also in sheet 2 under it which has been tablulated below for ease of explanation "i hope".
Basicly the names in cells B & C need to be tabulated in "sheet2" as shown in columns E,F,G. at present i was doing this by Ctrl and then paste into "sheet2" but now data increasing into thousands per title and taking hours (fingers tired) ...
View 9 Replies
View Related
May 16, 2008
How do I hide the name of Macros from viewing? Right now everything is protected, but users could see and run the macros.
View 9 Replies
View Related
