Need Macro To Create Formulas And Split (Comma Delimited) Cells Into Rows

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


ADVERTISEMENT

Create Individual Comma Delimited Cells From Delimited Cell

May 30, 2008

I need to create a comma delimited list based on variable start and end values for each row.

StartEndOutput List
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200220082002, 2003, 2004, 2005, 2006, 2007, 2008
200420072004, 2005, 2006, 2007
200420082004, 2005, 2006, 2007, 2008
200520082005, 2006, 2007, 2008
200620082006, 2007, 2008
200820082008

I'm not a VBA expert, or I would have created a Do While or For Each loop.

View 4 Replies View Related

Separate Comma Delimited Cells To New Rows

Mar 31, 2008

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

Separate Comma-delimited Values To Rows

Oct 22, 2009

I have an excel sheet I create with lots of inventory items distributed amongst several locations. The first column is a location field where several 1-2 digit location codes are seperated by commas. I would love to get the macro to copy each row, once for each location code, onto a new tab as a new longer list. Because the amount of data per row varies, I need the entire row to be copied to the new tab....

View 10 Replies View Related

Transpose Column Into Unique Rows With Comma Delimited Values

Nov 4, 2013

I am trying to execute a script I copied from this site to transpose a column of values into unique rows. There was a very similar thread to my question, but the code does not work for my situation as I am a newbie to VBA. The referenced thread was Need to transpose multiple comma separated values

I would like to comma delimit column "D" into unique rows per value while maintaining the relationship with the data in the other columns. Here is the example of my data:

GA-AG-00010-A-2013
Apache Hunting Club

709

36

GA-AG-00020-A-2013

Tiger Branch Hunt Club

1596

71, 72

GA-AG-00030-A-2013

Big "O" Hunting Club

3058

59, 64, 65, 75, 79, 84

I want to make each value in Column D a unique row and still be associated with Column A-C. When I run this code I get a Runtime script error 9.

Sub SplitKeywords()
Dim MyArr, v As Long, i As Long, LR As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row

[Code] ......

How to correct this? My actual data spans from column A-Q and can place the "split values" in column Q.

View 9 Replies View Related

Macro To Save Excel Sheet As Comma Delimited Text File?

Apr 21, 2014

providing a macro to save an excel sheet to comma delimited txt file. Also, My sheet has 1st row as table columns and i dont want to export them in my txt file.

View 14 Replies View Related

VBA - Split Comma Separated Entries To New Rows?

Feb 4, 2013

I have a relatively large data set that is separated into columns A-G. In column G, I have a multitude of values that are separated by commas. What I would like to do is to separate these entries into rows while keeping those values in A-F constant.

Here is an example of what I would like to do through VBA:

ex1.jpg

to

ex2.jpg

Also, kudos. This website has now gotten me interested in VBA.

View 6 Replies View Related

Split Single Text Cell Into Multiple Rows Using Comma Delimiter?

Jul 3, 2014

I want to split the contents of a single cell(ALT Enter as delimiter) into multiple cells and retain the values in column B.

Ex:

A1: apple
banana
car
house

A2: yellow

B2: building
x
y

B3: O

Output:

sheet 2:

A1: apple B1: yellow
A2: banana B2: yellow
A3: car B3: yellow
A4: house B4: yellow
A5: building B5: O
A6: x B6: O
A7 B7: O

View 9 Replies View Related

Split Single Text Cell Into Multiple Rows, Using A Comma Delimiter

Dec 1, 2008

I have a string of text in one cell on Sheet 1 (ie., A1, Sheet 1), here is a excerpt:

A-dec International Inc., A. Bellotti, A. DEPPELER S.A., etc ...

What I need to do is split the cell into separate rows, using the comma as a delimiter. I will be reading the cell from another sheet and need a formula that will provide me with

A1: A-dec International Inc.
A2: A. Bellotti
A3: A. DEPPELER S.A.

View 9 Replies View Related

Split Words In Comma Separated String To Cells In Column

Nov 1, 2012

I have extracted a string from my address database which goes like name,address1,address2,city,postcode,country

I need to display in Column B as:

name
address1
address2
city
postcode
country

how to do this using VBA.

View 2 Replies View Related

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

SUM Of Values In A Comma Delimited Cell

Jan 13, 2010

Is there a SUM formula I can place in cell B1 that will sum values I have in A1 expressed as 1,4,6,7. The number I should see in B1 is 18.

View 14 Replies View Related

Sort With Comma Delimited Numbers

Jul 7, 2014

make a macro wich it's going to sort comma delimited number in the correct order.. see attached file to get the wanted reult..

View 5 Replies View Related

Saving To The CSV (comma Delimited) File

Dec 1, 2006

I have a work sheet with some names address, and phone number in it... I need to save it as a CVS file (comma delimited) easy right??? WRONG!!! everytime I save it, it takes the phone number column and shortens it AND turn the phone numbers into a mess

What it looks like NOW
9057926500

What is looks like after save

9.06E+09

If is make the column bigger it goes back to the "good" numbers, but when I save to a CVS it goes to the "bad" number.

View 9 Replies View Related

Text File But Comma Delimited

Sep 28, 2007

In my workbook I have 10 columns with data, starting at A12 and down. This could be several hundred rows.
I would like to export the first three columns only (Column A, B and C). The TXT file should have the following:
First line: "This file was exported from Excel"
Second line: The value of cell B4 of the worksheet
Third line: The value of cell B5 of the worksheet
Fourth line: Today's date ( as 27/Sep/2007)
Fifth line: No entries (Empty row)
Sixth line will be the first numeric transfer. This is cell A12 value, cell B12 value and cell C12 value.
Seventh line: Cell A13 value, cell B13 value and cell c13 value.
etc, until the end or selected amount of rows.
The values of the different columns should be separated by commas
Column A values could be 9 characters (5 numerics with 4 decimals - 12345.6789)
Column B values could be 10 characters (6 numerics with 4 decimals - 123456.7890)
Column C values could be 10 characters (6 numerics with 4 decimals - 123456.7890)

The text file therefor would look as follows:

This file was exported from Excel.
Company ABC
On this continent
27/Sep/2007

123.4567, 23.7654, 123.4567
1234.5678, 123.4567, 987.6543
12345.6789, 6.0000, 2.9876

I tried to adapt jindon's code but no luck.
I am currently doing it the long way.
='[Workbook1.xls]MySheet'!$A12&", "&'[Workbook1.xls]MySheet'!$B12&", "&'[Workbook1.xls]MySheet'!$C12
This works but I really liked what jindon did and wondered if it could be adapted to fit my needs.

View 9 Replies View Related

Search Comma Delimited String

Dec 1, 2009

I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found.

For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years.

I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?

View 9 Replies View Related

Data In Column A To Comma Delimited List

May 31, 2012

excel spreadsheet that has data in column A like in the attached example spreadsheet. How can I make this into a comma delimited list? So something like this...

food
drinks
home
shelter
ocean
water

to this ---> food, drinks, home, shelter, ocean, water

View 5 Replies View Related

Splitting Comma Delimited Numbers Into New Columns

Apr 20, 2009

I am using excel at the moment with a card playing program. using the excel sheet they provided the details of what cards are dealt are exported to the worksheet and there is a simple table like so

Player Cards
............................................................
Player 1/ 24, 27, 16
Player2/ 1, 5

The information is fed through one number at a time as the cards are dealt for a total of three rounds sometimes it is only two rounds and are delimited by a comma all in the same column. I would like if possible to have these numbers appear in separate columns. that is

Card 1 / Cards 2 / Card 3
Player 1
Player 2

IS this possible. briefly i want this to happen so I can use the Vlookup function as the numbers that come through each stand for a card value but using Vlookup only the first number works and the following return an NA value as it is impossible as far as I know to have every possible combination represented in a table . If there is a way of tweaking Vlookup so it recognises the comma delimiter and in the vlookup column it will show all converted numbers then i'm all ears otherwise any help on how to split would be much appreciated. Quickly I did try using the text to columns function when i did this however in the new destination it showed only the first number and discontinued showing the others in the original as well. Additionally in this function the 'preview of selected data' does not show selected data but some sort of link =programme_name_card_gamecard_1 somethig like that. Sorry for the long one.

View 12 Replies View Related

Open All Txt Files (delimited Tab Comma ) In A Folder

Aug 11, 2009

I have around 30 files each quarter which I need to convert from a text file to an excel spreadsheet. I am a beginner with VBA and am looking for generic code I can use for a macro to:

1. open a text file from a folder
2. delimited/tab/comma
3. format columns H, O, and AH into dates
4. 'leave a spot for me to insert my code to manipulate the data'
5. save the file as an excel spreadsheet to a folder with the same filename
6. loop to perform this task to all files in a folder and stop after the last file.

The dilemna I am having is that my text files do not have a suffix ".txt" after them.

They just have the file name ***MMDDYYYY. There are always 3 initials at the beginning which change for each file i.e. ABC06302009.

The date remains the same for the given quarter, i.e. ***06302009. Next quarter I will have to do this same thing for all files ***09302009.

View 9 Replies View Related

Import Comma Delimited Info In A Column

Mar 4, 2006

I have hundreds of email addresses listed in a Wordpad file- entries are
separated by a comma. When I use the Excel Import function, all data is
successfully imported into an Excel spreadsheet- BUT in a single row. I would
like to have this info in a single column (A)- then I could easily
alphabetize the entries and eliminate duplicates.

View 10 Replies View Related

Convert Comma Delimited Cell To An Array

Feb 15, 2010

{= SUM(IF(({325,481,342,440,425}=ID)*($A37=DateRng)*1, ROUND(Sales,2),0))}
I am currently using this formula to retrieve total sales by day for each team and it works perfectly. Data is stored in columns by Date,Salesman ID,Sales.


I would like to replace the array portion with a vlookup to return the array set so i can use drop-down to select different teams and see the sales for that team.


{=SUM(IF((vlookup(TmName,Teams,2,0)=ID)*($A37=DateRng)*1,ROUND(Sales,2),0))}
This is the function as I thought it would work, but the vlookup returns "325,481,342,440,425" as a string not an array.

View 9 Replies View Related

Convert A Column Into Comma Delimited List

Oct 27, 2006

I have a list of terms in a spreadsheet. Assume they start in cell A1 and they descend down for the next 300 cells. Basically I need to pull those terms into a single text string where the terms are comma delimited.

What I have been doing is concatenating them so they all end with a comma, copy them 25 at a time, pasting values and transposing then running a concatenate formula for the 25 terms. Do this 10-15 times to create that many comma delimited lists then concatenate those lists to create one all in one list.

Example:

The list (starting in A1) looks like this:
Dog
Cat
House
Car
Boat
Mom
Dad

but I need:
Dog,Cat,House,Car,Boat,Mom,Dad

If possible to do this with a formula please do so as my knowledge of using VBA modules is limited but if this must be done using VBA please realize that I'm in the thrid grade compared to your knowledge so please explain how to implement the module as clearly as possible.

View 4 Replies View Related

Remove The Extra White Space Between Each Comma Delimited Digit

Oct 25, 2009

This is a delima I cannot figure out. I had to create passwords for a website we are building. I have 3000 employee numbers has to be used. So what i did was took the first initial and middle initial and last initial and first 5 of the ID number. I did a comma delimiter to obtain all of the letters and numbers. example: ABC12345

My problem is none of the passwords work because when I imported the letters and numbers into the sheet it looks just like the above. However on review I cut and pasted back to notepad and the data looks like this:

"A B C 12345"

So its adding a tab in the password thats thats a problem, How do I remove this extra white space between each comma delimited digit? without having to manually delete it ?

View 3 Replies View Related

Pivot Table Labels Based On Comma Delimited Cell Values

Jan 25, 2010

"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.

The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."

I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!

View 2 Replies View Related

Convert Multiple Delimited Cells To Rows?

Mar 12, 2013

I have tried to alter some existing code from this site to accommodate my needs with no luck.

I would like a macro to convert the following.

Column A1-C1 contains Headers Column A-C contains Simple data to be copied down as many rows as needed to match the number of delimited pieces in the following columns. Columns D-I contain Data delimited by 3 spaces. The number of delimited pieces of data can be different in each row. The number of Delimited pieces of data will be the same in each column of a particular row. Example: if D4 has 4 pieces of data separated by 3 space, E4 will also have 4 pieces of data separated by 3 space, F4 will also have 4 pieces of data separated by 3 space and so on to I4.

D5 may contain 5 pieces of data separated by 3 spaces through I5 all containing 5 pieces of data.

Column G may contain a single space within the data delimited by 3 spaces.

Columns D-I may only have 1 piece of data with no delimiter.

I would like the Delimited data to be split apart and a new row created for each piece of data. and copy the corresponding data in column A-C to each new row.

If this can be done in the same worksheet or copied to an existing worksheet that would be great.

Here is a link to a sample on Google Drive [URL]

I am using Excel 2010 but would like it to be compatible with 2003 as well.

As I mentioned I found some VBA to do what I need but it only does 4 columns and I cant figure out how to make it do the 9 I need.

View 7 Replies View Related

Split Concatenated Delimited Cell Values Into Separate Columns

Jun 20, 2014

Im trying to come up with an automated way of splitting concatenated information but putting each concatenated value in a seperate row.( i have columns of data that need to stay with each concatenated value) Eg

Concatenated comma delimited data

Cust Name Cust Identifier Locations
L Kim 543545 California,Chicago,Florida
S David 31434 Maryland,Vermont,Maine
D Bryant 572575 Texas,Oaklahoma,Nebraska

i need to to get a result that looks like the below example im currently just doing text to column filterting and copying all the columns over and stacking them on top of each other. i need to find a way to automate this process but i cant seem to think of one

Customer name Customer Identifier Location
L Kim 543545 California
L Kim 543545 Chicago
L Kim 543545 Florida
S David 31434 Maryland
S David 31434 Vermont
S David 31434 Maine
D Bryant 572575 Texas
D Bryant 572575 Oaklahoma
D Bryant 572575 Nebraska

View 2 Replies View Related

Split String Separated Comma

Oct 9, 2013

In filed I have couple of value separated by comma like below:

A1 header1
B1 header2
C1 header3

Audi
592035, 579733, 653749, 579735
20 000

If my macro found that string (always will be separate by ",") should split the string and add rows (= to number of string). The output should be as below:

A1 header1
B1 header2
C1 header3

Audi
592035
20 000

[Code] .....

I have:

Code:
Set sourceWb = ActiveWorkbook
Set ws = sourceWb.Worksheets(1)

Dim LastRow As Long
Dim MY_Split As Variant

LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

[Code] ...........

View 1 Replies View Related

Split Multiple Comma Separated Columns Using VBA?

Jan 18, 2012

I am supposed to create a macro which will split the comma separated values into rows.

My main constraint is, I have 3 columns of comma separated values.

Let me explain with an example,
I have 5 columns namely:
A B C D E
123 1,2,3 1,2,3 123 1,2,3,4

Once I run a macro it should show like below:

A B C D E
123 1 1 123 1
123 1 1 123 2
123 1 1 123 3
123 1 1 123 4

[Code] .......

View 6 Replies View Related

Dropdown List From Comma Delimited List In Single Cell

May 9, 2014

Is there a way to create a drop down list from a comma delimited list in a single cell? For example, col A is Name & Col B is the delimited list - Blue,Red,Green (list can be different for each name). Would like a drop down list in col C that allows you to pick one of the values from Col B.

View 3 Replies View Related

Macro To Split Rows Into Groups Of 5 And Insert 3 Blank Rows In Between

Feb 9, 2013

I would like to have my macro code search column A (supplier numbers) and split the rows into groups of rows of 5 or less and then insert 3 blank rows between each group of rows. The split needs to start on a new supplier number and cannot split a supplier number into two different groups. Here is a sample:

Supplier
Invoice Date
GL Date
Invoice Amt

[Code].....

View 1 Replies View Related







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