I am receiving a new report from an external system. Unfortunate the system delivers many information within one cell, and it is not possible to reconfigure. These information are separate by a semi-colon or a semi-colon + space. separate the information in separate cells?
I have a mass of data which look something like this:
and I require the ranges of reference numbers to be listed in a column one above the other, which requires inserting new rows. I also need the date & description columns copied down into the newly inserted rows.
So basically for example I would want the top row to now read:
and then apply the same procedure to the other ranges below this.
I have cells of block addresses that are delimited (tab I believe - there's a small square that appears).
I need to split the addresses out into separate columns for each line of address. I tried using the Text to Columns wizard as suggested by Excel Help, but ended up with only the first line of address being posted in the cell to the right (the rest of the address was nowhere to be seen).
I'm sure I've done this before but can't for the life of me remember how I did it.
I have a group trip coming up where we took in a $1000 deposit per person. The total amount is only in the main members cell. There will be two more payment amounts coming. I need to make certain each person has paid the total amount they are required for the trip. There are two possibilities for total trip amount. Double occupancy is $15,500.00 and Triple is $14,500 per person.
There is a unique Identifier for each registrant, except when there is a multiple person under one registration the identifier has all the same number except at the end. I figured out a way to strip the number down to just show the same number in one registered group. There could be up to 10 people under one registration. I might not have the best option for breaking down the Identifier number, but thought it would be a good start. Used the code Left(column, number).
Issue is this. How can I now take the total number of people under one group take the total deposit and split it between just that unique group so I can then determine what is still owed after payments are made.
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!).
I was just not able to figure out how to write a macro for splitting the cell data into multiple rows with the other column values being duplicated. Just elaborating my problem, its like i have a column in which i have multiple values separted by a comma, So i need to divide all these values on basis of a comma and then copy this value to a new row below, along with the previous column values same for this new row. in my file cost center column is to be worked upon. Can this be done by a macro ?
how to read mutiple values from one cell. For example i want to have one cell for site visits, and there may be more than one. So i want to let the user enter more than one date seperated by a ",". Is there anyway of going about this?
I have a situation where I have source data formatted like this:
Document ID Document Name Author
What I would like to do is to get each author on a separate row. I am able to do that using Text to Columns using the carriage return and then doing a transpose. I've also seen some threads where VBA is used to accomplish similar. However where I am stuck at is getting everything else to drop down accordingly. I.e. with the data set above I'd like to get to:
the question: i'd like to split a large cell in to smaller, fixed-size cells (columns) by a standard formula.
example: consider cell A1 = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ' i'd like a formula that created B1 = 'ABCDE' C1 = 'FGHIJ' D1 = 'KLMNO', etc
I used =Left (A1, 5) and got A1 = 'ABCDE' easily enough. I'm stumped at coming up with a formula that provides the next 5, and the next 5, etc.
the setup: if you want to know why i really need to solve the question i'm asking rather than using the split cell function:
i have a large set of data from a vendor. ideally, this worksheet will be used for other vendors that submit data similarly.
the script i'm writing takes a specific format--which i can't logically hold the vendors to. so, my specific formatting is on one sheet, referencing the data from each vendor on another sheet.
there is one field of data from vendors that is large ( >4000 characters, typically). however, this field makes my SQL server puke. i need to break this field down in to chunks of 1000 characters, and then re-concatenate after it leaves SQL to its final destination.
because i want to use some script automation to reuse this project, i'm reluctant to just use the split cell function to break up the data. rather i'd like to use whatever data is provided, and referentially split it up in my pre-formatted data.
Ihave data in column "B" like bellow & i want split that one cell data to different cells base on space. i mean after each space data should add to next cell ( from column "C" data can split). (formula or macro)
I have many columns in my current spreadsheet but I have two columns that I want to split in to two project names.
Currently I have one column with: Project A/Project B And the second column with the total cost of Project A AND Project B combined
The thing is that I can easily just create a new line but that would mean all the data in the other Cells remain the same and the only two that will be different are the above two columns. I will be maintaining alot of rows so I was hoping I do not have to create two rows for every new item then to merge both rows in reach column every time I have to put in a new Item.
I am hoping to have for example the below
Name Comments Project Cost ------------------------------------------------- Item 1 This item relates Project A $150 to upgrade of Project B $120 SAS system ----------------------------------------------------
Currently I have it as
Name Comments Project Cost ------------------------------------------------- Item 1 This item relates Project A/B $270 to upgrade of SAS system ---------------------------------------------------
I have some data that is imported, it's about 25,000-30,000 characters and 2,300- 3,500 words, it all comes in as one cell, A1, if I do a text to column on it I will loose everything past column IV. Is there anyway a macro can take the data and put each word in a cell by its self starting in A2? The words a have a space between them.
I have a spreadsheet that I have to extract from a web based report at work. The problem is that the column that contains the department name also contains the department code in brackets. For example cell A2 will have: (DE) Department A and cell A3 may be (DEPT) Department B.
Now as seen in the example above a code can range from 2 to 4 letters. I wondered if there was a way in code that would search for the ')' character and copy that to the M column and then copy the department name into the N column. So M2 would have 'DE' and column N2 will have 'Department A'.
Also in column G contains the grade name and grade point which are separated by a '/' I would like this to be copied to 2 separate cells say O2 and P2 and so on.
I know this can be done by a formula but I have to do this every month and it would be nice to just click a button to run a macro in my personal.xls file.
The row number various and are usually several thousand. I have attached a sample spreadsheet that only contains 4 rows of data.
I need to make sure that splited cells do not replace content of other cells. For example, if A1 has "SDR232, SDR634", this code will split them and put A1 to be "SDR232" and A2 to be "SDR634". BUT what it does is that it actually replaces whatever I have in A2. Is ther anyway to avoid replacing the content of A2?
Sub tst() Dim X As Variant X = Split(Range("A1").Value, ",") Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X) End Sub
What I got going on is a program I use for reporting is shooting out information.. while most of the information it sends to an excel workbook is sectioned by cell, there is a good chunk of information that's dumped into 1 cell. Is there a macro, or something, I can do to separate the information in this single cell, into separate cells? Auto Merged Post Until 24 Hrs Passes;Here is an example of the report. You'll notice a bunch of information in the 'Description' section. What I want to be able to do is run a macro that will seperate the 'Item Number', 'URL', 'Serial Number', 'Model Number', and 'Details' information into seperate cells.
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
I would like to split a worksheet to multiple sheets based on a column header. On browsing through the forums I found the VBA code below.
My problem with is that the code automatically uses only the first column for spiliting into different sheets. But I would like to modify this so that it searches the first row for a matching header specified by me (Eg "Name" , which may be column 10)
Code: Private Sub PagesByDescription() Dim rRange As Range, rCell As Range[code]....