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?
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
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?
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 RelatedI 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.
I would like to "reverse concatenate" an address text string as follows: ....
View 9 Replies View RelatedI 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.
example, if cell A1 contains: 13 Salisbury Street Balwyn, I would want Salisbury Street Balwyn to be put in cell B1.
View 10 Replies View RelatedHow 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.
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
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.
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
I'm sure this is an easy one, but I have never done this before. I need to know the best way to hide (for example) Columns U:W if cell U3 is blank.
View 9 Replies View RelatedI 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.
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 Relatedevery 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
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.
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] ......
Is there a way to separate the suffix from various addresses i.e
A1= FOSTER AVE
A2=WISE
A3=EL DORADO ST
A4=OLD HIGHWAY 221
Some of the addresses are up to 5 words and numbers, some are missing the suffix all together.
The following code takes a large range of data (currently 20K+ rows) and breaks it out into separate worksheets. This takes a while, and I have been trying to insert a progress bar into this macro, but the progress bar goes in reverse (from 100 to 0) but the userform will not unload at the end.
Sub MoveCells()
Dim objBook As Workbook
Dim objSheet As Worksheet
Dim lngRowSpace As Long
Dim strName As String
Dim lngTimeRow As Long
Dim lngStartRow As Long
Dim lngInteration As Long
Dim strDataSheet As String
Dim boolError As Boolean
Dim counter As Integer
Dim PctDone As Single
I am looking to split data in a worksheet into separate sheets based on Column C which contains Either Babycare or Homecare or Industrial or Professional. Eventually there maybe more names. If possible I would also like to name each sheet.
My VBA skills is limited to this particular task.
splitting out date/time strings to separate cells, but I cannot find how to split the following.
I have the serviceable date of an aircraft delivery set as 'S hh:mm dd-mmm-yy', which is due to being used by an external application in this format. This is entered by the user and I need to be able to extract the date time strings and switch them to dd-mmm-yy hh:mm, excluding the S from the result as a formula in another cell.
I keep getting a #value error or returning the whole string result.
This formula I want to apply it in another workbook. It split in different columns the content of a cell.
The formula is below:
[Code] .....
In cell A2 I have the following data:
|516582-001-99|414816-001-99|414816-003-99|516582-001-99|
If I apply the formula above in cells B2 to E2 it returns a blank cells. But if I delete the first "|" sign in the left side manually the formula works perfectly by splitting the cell into columns from B2 to E2. The issue here is that I have more than 300,000 records. Just imagine the amount of time invested in just deleting the first "|" at the left side.
I need a variation of the formula above that in first place delete the first "|" at the left side and after that continue with the proper work of the formula.
I receive an extraction from AutoCAD that lists the electrical devices in a drawing. I don't have any problems extracting the letters. I have a problem extracting the device number and the device number extension.
The device label extraction is similar to this:
DCM1005-1
DCM1005-10
DCM1005A
MTR1005-1
MTR1005-10
MTR1005A
I want to create 3 columns from the device label: (I separated the column with commas)
A1, B1, C1, D1
DCM1005-1, DCM, 1005, 1
DCM1005-10, DCM, 1005, 10
DCM1005A, DCM, 1005, A
MTR1005-1, MTR, 1005, 1
MTR1005-10, MTR, 1005, 10
MTR1005A, MTR, 1005, A
How can I separate the following numeric/text combination into two (2) separate columns in Excel?
302ALTO
406AMZN
451AMRC
404AMAD
605ANCC
405ADRC
The result would be:
302 ALTO
406 AMZN
451 AMRC
404 AMAD
605 ANCC
405 ADRC
Hi everyone,
I've got several Excel files that need to be edited separately.
Once they have been edited, I need to paste the content of each file in a single separate Excel file.
Is there a way to retrieve the content from all the files in a folder, and merge it in a single file?
For example:
3 separate files > File1, File2, File3
Merged file, sheet1
File1
File2
File3
how to separate data such as this, into different columns?
85878; null;OMX;OM;2004-09-13 08:58:29.0;691.91;OMX Stock Index;693.01;688.67;691.91;15055;0.0;14.64;0.0;0.0;2004-09-10 00:00:00.0;0.0
Today this data is inserted into one cell, in one column, but in 50 000 rows (in 9 different sheets!). I would like to separe it into separe columns so I could run calculations on the data. Separating it manually would take me at least 1 year,
I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet.
Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location.
VB:
Sub Create_Subfiles()
Dim FDMName As String
Dim FBName As String
Dim DIYName As String
Dim WMName As String
[Code] .....
I need to write a macro to split an excel file into separate workbooks and automatically email our project managers.
View 1 Replies View RelatedI have a data set that I wish to look up the data from one column and if it is greater then 0 write it in another column separated by commas. Here is an example:
The data is dates that a service was provided and how many time that day it was done and not everyone gets the service on the same days. I would like to summarize the days of the month that service was provided not number of times into 1 cell.
A B C
Row 1 November
Row 2 1 5 15
Row 3 1 0 2
Row 4 0 1 3
November is in A3
If A2 is greater then 0 I want to write A1 A2 If A2 and A3 is greater then 0 I want to write A1 A2, A3 If A2 is 0 and B2 is greater then 0, I want to write A1 B2