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
ADVERTISEMENT
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
Aug 12, 2014
In A1 I show the following content: 5,12,27,36,124
How can I make this split to show the following layout:
A1: 5,12,27,36,124 B1:5 C1:12 D1:27 E1:36 F1:124
My worksheet has thousands of lines so hoping for a quick and easy formula that I can pull down and copy for the entire sheet?
View 7 Replies
View Related
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
Mar 6, 2014
I have a string of data coming from a SQL Server data connection into my workbook. The value in the column is a text string that is pipe delimited. I need a macro to parse the data from that column into applicable separate columns. I would typically use text to columns for this and parse it out manually... but the tool I'm creating is one of the automated variety, so that will not suffice for this application. I need it to do this automatically when the data connection refreshes.
U:U AQ AR AS AT AU AV
to
Produce|Fruit|apple|banana|cherry|date Produce Fruit apple banana cherry date
View 2 Replies
View Related
Aug 20, 2014
After I imported this data, the date and time is in the same column in the format of "mm/dd/yyyy hh:mm:ss" military time. How do I write a VBA code to split up the date and time into two separate columns. One column would only have "mm/dd/yyyy" while the other only have "hh:mm:ss" in military time.
Eventually, I need to extract information from the data by looking for a specific time. I would also plot time vs something.
I don't know if treating it as a string would work, because it would just become a text rather than a time, right?
View 10 Replies
View Related
Apr 26, 2007
I have a spreadsheet with a few thousand rows, yeah i know..lol Each one of the rows has contact details for individuals. I have the address for each person in one field with up to four different sections, seperated by tabs. How do I go about seperating each part of the address so it is in a different column? I have tried text to columns, and it only seperates the first portion of the address, seperated by the delimiter 'tab'.
View 9 Replies
View Related
May 22, 2014
I need to split the data into worksheets (see attached) by the Advertiser column and then by the deal year and deal code columns. I need each worksheet to be named per advertiser and deal year_deal code. I took off and replaced the data since it is sensitive information.
creating a macro or implicating one into the workbook to run for future reporting.
View 13 Replies
View Related
Mar 13, 2009
I would like to "reverse concatenate" an address text string as follows: ....
View 9 Replies
View Related
Dec 1, 2012
I have a mileage expenses form from work that I am trying to fill in using Excel, instead of filling it in by hand.
Column R shows the miles travelled.
Column S shows the Rate per Mile.
Column T (should) show the pounds.
Column U (should) show the pence.
Searching has led to T11, for example, using the formula below to show the pound value.
Code:
=INT(R11*(S11/100))
However, I am having less luck on finding a way to display the pence amount in U11.
Ideally, I would like the cells in column U to show the pence without a leading zero or decimal point and just the pence value only.
View 7 Replies
View Related
Jun 19, 2014
I have charts that I have to update at work, and I want to make it as fast of a process as possible. Here is an example of what I want to do.
I want to put the letter B in cell 1, then the number 23 in cell 2. I then want to concatenate those two strings, which I can do. I end up with cell 3 which says B23. I then want cell 4 to to be the value of cell B23. If the B23 in cell 3 changes to C11, I want cell 4 to be the value of cell C11.
I would use an IF(x,x,x), but I want to to take into account the fact that it could be any cell on the whole sheet which would be one hell of a nested IF statement.
I need to do this without macros if possible.
View 3 Replies
View Related
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
Apr 8, 2014
I have the cell data as below
How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?
I still want the original data as I need to check that the splits worked well?
16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5
View 2 Replies
View Related
Oct 13, 2008
I'm trying to compare values in 2 separate columns to see how many times the same value appears in both columns. Ideally I would be able to insert a range function to compare the values in the column "ID 1" against the values in column "ID 2" and return the count of times that a value appears in both columns. For example 2122, 1112 and 1718 appear in both columns and I would like the formula to return a count of 3.
ID 1ID 2
12342122
45671112
89101718
11122678
13144544
15162324
17189987
19201215
21221928
1976
2576
2345
4678
In my actual project I'm comparing 2 columns in the same worksheet. The column are column B with data in cells B2:B10266 against column C with data in cells C2:C18560.
View 4 Replies
View Related
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
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
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
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
Oct 13, 2009
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 3 Replies
View Related
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
Sep 15, 2013
I have some data with recurring key values and differing values in the second column, I need to produce a unique list of key values with the second values concatenated together.(See below)
The data can be 10 rows to 5000 and I can have anything from 5 to 150 sheets (Separate data sets), a macro would go a long way to keeping me sane.
Sample data Required Output
A | B Z
1| 10 | a 10,a,b,c
2| 10 | b 11,a
3| 10 | c 12,a,b
4| 11 | a
5| 12 | a
6| 12 | b
My system is Windows 8 Excel 2010.
View 7 Replies
View Related
May 8, 2014
I am trying to put something together on Excel.
1. In my first column(Let's call it "input"), there will be a variety of values from 1-10 which will appear in cells as 1,7,10 or 4,5,6,7 or any other similar variation. The quantity of numbers can vary and will always be comma delimited.
2. Following the first column are 10 additional columns. For simplicity, let's say they're labelled 1-10.
3. If 2,5,6,9 is present in the first cell, I want the number 1 in the columns labelled 2,5,6 and 9.
If 1,4 is present in the first cell, I want the number 1 in the columns labelled 1 and 4.
The input will only be in the first cell so I am hoping to find a way to automate the placement of 1's in the appropriate column depending on the input in the first column("input").
As a bonus, I'd like all cells not present in the first column to appear as a "0".
View 14 Replies
View Related
Dec 3, 2009
I have a report that I need to reformat where part of the information is moved from rows to columns.
The report is broken up into "sections" as follows: Each “section” is a series of multiple rows and is broken down as follows:
ROW 1: Contains data (in a single cell) about a Sales rep, which includes (1) rep number & (2) rep name
NEXT ROW(s): Contains data information about an invoice(s), which includes date, invoice number, client name, trans ID, etc. The invoice data can be one row up to as many as 500 rows
LAST ROW: Contains the Rep Subtotal
I need to spit out a report that contains the invoice data only (the middle part of the “section”). I don't want "ROW 1" or "LAST ROW" of each section in the output. For each invoice row, I need to include the rep number and the rep name for each invoice. As noted, the rep number and name is always listed in the row preceding the invoice data. The format is always a 6-digit code followed by the name. So I need to split the data into two pieces.
View 12 Replies
View Related
Sep 1, 2009
I am trying to split data from a cell into two columns. I have tried the LEFT and RIGHT functions but as the data held is not always the same number of characters this does not work for the LEFT function. The only constant thing is that the last character will always be in the second column. An example of data would be:
9065-10A
9065-9B
9065-11C
and I need to be split into 2 columns as follows:
Column 1 Column2
9065-10 A
9065-9 B
9065-11 C
View 2 Replies
View Related
Jun 22, 2013
I have a list of user names and UIDs, all in one column. I'd like to split the numbers into a separate column. How can I do this? Here's an example:
A'Aross Hd 798027047
A'Yolanda Gallegos 100004367799914
Aaiky Sweet 100000984883871
Aarchie Alin 100000295447271
Aasusana Azanza 100000086329219
Ab Raf 100000223369007
Abigail Cadenas 100003769100097
Abigail Gopaul 100002988007633
Abod Rezk 100002010187332
Btw, I'm an Excel newb and don't know a lot. I did try Data/Text to Columns with a delimiter of spaces, but there's really no rhythm with the spaces so it splits it all whacky.
View 9 Replies
View Related
Sep 26, 2013
I would like to separate data into multiple workbooks based on a unique value in the column. For instance if I have Departments in Column F, that has data of accounting, HR, etc. I would like to put all accounting data into a accounting workbook and all HR data in a HR workbook.
View 4 Replies
View Related
Sep 25, 2008
every month I import (from SAP) Local Authority staffing data for 200 schools into an Excel template. After a bit of tweaking I end up with a single spreadsheet showing formatted and adjusted data for all schools, with relevant headings separated by page breaks.
This is fine if for distributing a hard copy to schools, but I want to email it. I've seen macros that would allow data to be split onto separate tabs, but is there a way to break it down into 200 separate spreadsheets? I confess to not knowing any VBA, so be gentle with me
View 9 Replies
View Related
Nov 27, 2013
I need to do a vlookup of the values of Sheet 1 Column A (Ref) in Sheet 2 Column A (Ref).
The results will be the concatenated values of Sheet 2 Column B + C + D + E to be displayed in Sheet 1 Column B (Address).
I'm not sure how I can do this using formula.
I've attached a example.
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
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
