Reformat a range of text in a spreadsheet. The text is currently in the following format:
cell A1: "ABCD"
cell B1: "A1 - A5"
I need to convert the range in cell B1 so that the range is expanded downwards in column B. The output should look like this:
A1: "ABCD" B1: "A1"
A2: "ABCD" B2: "A2"
A3: "ABCD" B3: "A3"
A4: "ABCD" B4: "A4"
A5: "ABCD" B5: "B5"
I am trying to figure out a problem. So far without any results. As an Intern at a company, every week I receive a .txt which I have to import and reformat in Excel. Every week this means several hours of work, so I decided to see if this process could get automated. Or at least partially. I was wondering if any of you could give me a hint how to do this, or where to start. Attached you will find the raw .txt data and the format it should get in Excel.
View 3 Replies View RelatedI got some vb code from these forums to use with excel. But, I'm not sure how to run the code in excel. Here is the link to the topic:
Move Text Layout To Different Format
Basically, I too need to move text to other cells within excel. The code listed is exactly what I need, but I can't figure out how to run it in excel. I'm not an excel expert or vb expert obviously.
I am trying to convert text to rows but in the same column Take the following example:
Column A:
1-2-3
ab-cde-fg
54-ty-12345
the
waht i am looking is convert it to the following format: (the - is the delimiter)
Column A
1
2
3
ab
cde
fg
54
ty
12345
i have found the following VBA code on those forums but i don't know how to edit it to suit my needs
Sub Txt_To_Rows()
Dim arrText() As String
Dim varItm As Variant
Dim rngText As Range
Dim rngCl As Range
Dim i As Integer
Dim j As Integer
Dim x As Integer
Set rngText = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
j = 2...........................
I have several hundred text files which I would like to reformat and save as excel without needing to open each file individually.
I'm entirely new to working with macros, so, I was able to use macro recorder to save the steps to reformat the file, but would like setting up the part of the macro to automatically open and reformat all of the files.
I copy data from word document to Excel and it will look like this:
Name
Address1
Address2
phone
fax
e-mail
website
I have like 500 sets of this above data.
When I copy & past to Excel, it is all populated into Column A.
But I wish to put Name in Column A
Address1 in Column B
Address2 in Column C
etc.
when i download the data to excel it is downloaded in some format i cannot use.
View 6 Replies View RelatedIm currently working with some data output from citrix and am finding that the system generated reports are not very user friendly. The format of the reports is as below (example only contains five products. The real report contains 500+ product IDs).
System Report Format.xls
Can anyone suggest a macro that copies and pastes data from the system generated report to a diff worksheet with a format such as attached:
New Format.xls
The challenge is that each of the product codes in the system generated report have two rows of data (recommended orders & open orders). Will it be possible to have the macro to SUM up the values in the same period of time in the new worksheet under the new format?
Additional Info:
1) Number of product codes changes monthly
In Cell A1 I have a company name which I wish to keep in cell B1 I have company address data I wish to keep and in cell B2 I have more address data which I wish to move to cell C1. Once this cells data has been moved I need to delete the two empty rows and then perform the same process on the next chunk of company data which is in the same format as the above and so until the end of the entries - sometimes potentially there could be hundreds of entries. I have attached an example of my problem - in the 'Original' worksheet is data in the form that I recieve and need to re-format. In the 'Fixed' worksheet is data in the format which I would like to have a macro to change it.
View 2 Replies View RelatedI have a worksheet that has similar data but some maybe different lengths. What im trying to do is to be able to have them all in line so where the diagram no. is on the worksheet they are all lined up. On the excel sheet attached I want TURN NO in column J to line up with turn no in Column A each time.
View 2 Replies View RelatedI m trying to use a variable that is declared as a range in a formula. GroupCategory is the name of the sheet its getting the data from. I want to use myRange instead of actually putting in the range. I know there's something wrong where I highlighted in red.
Sub AAmacro()
Dim myRange As range
Lastrow = Cells(Rows.Count, 1).End(xlUp).Row
Set myRange = Range(Cells(2, 1), Cells(Lastrow - 1, 1))
Sheets("sheet1").Select
Range("L2").Formula = "= SUM(INDEX(GroupCategory '!' & myRange,,MATCH(C1,GroupCategory!$A$1:$ AI$1,0)))"
End Sub
I have a fairly simple problem that my typical method of recording a macro and editing it isn't working for. What I'd like to be able to do is point a macro to a certain spreadsheet, and have it reformat the text of that sheet into a new workbook. I get new sheets from a customer each week, so the name of the sheet isn't constant. The process isn't too difficult to do by hand, but the sheet I'm trying to reformat is typically 30 lines or so of data, which is cumbersome to do manually line-by-line.
I've attached a sheet with a sample of what the data starts out looking like and what it should look like in the end. My goal is to take a single row of data and turn it into a check sheet so that there's a check box between each part number and each process. The number of parts can be anywhere from 6 to 17, but the number and types of processes is always the same.
I've attached a small sample of an excel file I created from a CSV. The date has defaulted to American format and the time is, as you can see, messy. How can I reformat so that I can have British date format and 24 hour clock?
View 2 Replies View RelatedI have a spread sheet that I'm using to organize data from a web based database. I have a formula that shortens a document number (i.e. 93400034) to a julian date (i.e. 9340). My problem is that when I copy and paste the 2010 document numbers (i.e. 00240057) into the spreadsheet excel automatically changes the number to 240057. I've tried to format the cell to read it as text, but everytime I paste into the cell excel changes it back to general and takes off the leading zeros. Is there a fix for this.
View 4 Replies View RelatedHow should i reformat the date and time picker? at this moments, it shows the date in the format of mm/dd/yyyy but the format that i want is dd/mm/yyyy.
View 10 Replies View RelatedI need to extract and format a large data base of county real estate tax parcels for a report. See attached. How can I combine these or is it possible.
View 2 Replies View RelatedI have a worksheet that has a column of numbers entered as 1234567. The person that input these numbers should have done this: 123-4-567. Is there a macro that could do this automatically so I don't have to go into 1150 cells and manually change the format of each of them?
View 2 Replies View RelatedI have 447 worksheets in my workbook, I need to consolidate them into one sheet and transpose the data so that it goes into across rather than down. I really only need data in rows 19 to 40 but the data in 29, 30, 31 A to I needs to be in one cell.
View 5 Replies View RelatedI'm trying to take spreadsheet data in columns and reformat into rows. See attachment. Basically i want to duplicate columns A thru E by row specific and then take columns F thru K in pairs to match up with the duplicated rows corresponding to the specific rows. So every row currently will become 3 rows (A thru E) with Columns F and G (old F & G row 1, H & I row 2, J & K row 3)
View 4 Replies View RelatedI have a spreadsheet where I extract the last 3 digits of a five digit string by using the "=Right(...)" command.
Column A 71500
Column B =right(column a,3)
Column C 500
Column D = lookup(Column C,toc_desc)
Column E #N/A (if it worked, this would read Labor)
I want to take the result of that command (Column C) and use the "Lookup(...) command to translate type of cost as a name rather than the three digit number (Column D). Excel doesn't recognize the result of the "=Right(...)" command. I am getting #N/A as a result. (Column E). How can I get Excel to read that number and give me the results I need?
I would like to state how fantastic a resource this forum has been to me as a beginner. So keep up the good work! The issue I have is that my spreadsheet contains one column which shares two types of data: Component Type and Tag Number. What I would like the script to do is:
1. create a new column
2. move the Tag Number data from its current column into the newly created column
3. ensure that the data is still on the same row as it was previously
One thing to bear in mind is that the tag numbers consists of various formats (spaces/no spaces), but always begin and end with brackets - "(XTU U-532-934)". I have included and example of the current state of the data and what I would like to look like after.
I have no VBA knowledge but need to write a macro to look at each worksheet in a workbook , search for a cell with " Total" text, then format the cell upper and to the right as underline.
I tried to record "find" but nothing happened. Is there some existing code I could adapt?
I have a sheet that has multiple records of the same recurring format (see attached .xls) from which I must extract data from the same groups of cells in each record (highlighted in yellow for example purposes) and export to a columnar format in another sheet. The only variable with the format is where we get into the amount of people on the plane and thus the line may shift down one or two spaces. I would like to just pull the data for the same person's name from each record as well. What is the easiest way to go about doing this?
View 9 Replies View RelatedI have created a simple address book in Excel 2007, but it is not reader-friendly: My header row in Sheet1 consists of cells labeled “Category”, “Company Name”, “Street Address”, “City, State, Zip”, “Contact Person”, “Office Number”, “Cell Number” so the data for each company is listed in a single row. I would like to see if there’s a way that I can setup the workbook such that the data from Sheet1 automatically reformats into a directory-like format in Sheet2. Ie:
Company Name1 Contact Person1
Street Address1 Office Number1
City, State, Zip1 Cell Number1
Company Name2 Contact Person2
Street Address2 Office Number2
City, State, Zip2 Cell Number2
etc
Also, I would like to figure out a way to filter by the Category column, so that Sheet2 contains all items on Sheet 1 that are labeled Category A, Sheet3 contains all items on Sheet 1 that are labeled Category B, etc.
I have an extract file that is in excel and lists contract numbers with a payment amount to the right. Each contract has multiple payments by period and thus there are multiple rows for each contract representing the multiple periods. The number of periods for each contract varies. The extract file contains multiple contracts. What I would like to do is have the contract number listed in one row and copy, paste, transpose the payments to the right of the contract number. The resulting file or sheet will have only one row per contract. The issue is that the code will have to recognize when the file number changes. I know I can do this manually but with 10 files and over 450 contracts this is a bit time consuming. Attached is a sample file with two tabs titled "Extract File" and "Desired Format". Extract File - is a sample of the main file that contains the data. Desired Format - is a sample of what I would like the final result to be. It can be in another sheet or another file.
View 3 Replies View RelatedI'm trying to write code in Airport1.xlsm to allow me to create a macro to automatically copy data from Airport-Data.xlsm and reformat it to what is needed in Airport1.xlsm.
Basically I need the code to take each airport in column A of Airport-Data and where there is a non-zero value in rows column C to G I need it to clear the appropriate column in Airport1.xlsm and add a 1 to the appropriate box.
I got stuck thinking about the nested For Next Cell in range procedure and how to execute it with a search for the correct row in Airport1.xlsm.
I'm using Excel 2003, version 11 and am trying to reformat data from one sheet to another. What I would like to do is copy the rows for each team from the Input sheet (Input tab) and and use them to build rows on the Output sheet (Output tab) for each possible combination of players.
In my attached example, a three person team has three combinations and a five player team has ten combinations and so on. I know this is probably a loop within a loop but I can't seem to get the right combination. Not all teams are full yet so the number of players on a team will vary. I need macro code to make the input sheet look the output sheet.
I am using someone else's old spreadsheet.
This spreadsheet uses a custom format of ##-##-## for a date such as 012214 prints as 1-22-14.
I need to convert this into normal short date format such as 3/24/2014 because there are four columns of dates in the spreadsheet and we have to do some addition and subtraction from one column to another.
I am using Excel 2007.
Rows 1 to 12 of the attached .jpg file shows the format that I receive from a University. However, in order to check enrolments, etc., I would prefer it to be in the format as shown on rows 15 to 19, that is, one complete student record per row.
As you can see, not all students are studying the same number of subjects and the subject mix is also different.
My fixed asset software will not allow me to show multiple individual months of acquisitions. I can export each month. This will create a separate spreadsheet for each month, but the problem is that each spreadsheet may contain the same asset, but additions to that asset.
Spreadsheet 1
....Description............................Dept...................January acquisition
.....Building____________________Plant______________1,000,000
Spreadsheet 2
.....Description..........................Dept...................February acquisition
......Building___________________Plant______________1,000,000
.....Welder____________________Metal_________________5,000
What I want is this
Spreadsheet 3
.....Description.........................Dept..................January Acq..........February Acq
......Building__________________Plant____________1,000,000__________1,000,000
......Welder___________________Metal_________________________________5,000
