I have a software package that requires serial number effectively data to be entered in a particular format.
As this can cover hundreds of lines I would like to make it less tedious to enter, and as my MACRO knowledge is very basic.
The data starts off in format below in example 1. After the data is CUT from the .html or .pdf document and PASTE into EXCEL. I would like the MACRO to start by pressing an activate button within EXCEL,
The serial numbers always have four digits with single serial numbers being separated by spaces and ranges being separated by a hyphen with the odd carriage return depending on how many numbers there are.
I would like the data to end up in two separate columns as shown in example 2.
Example 1 (Starting format)
* indicates space
- indicates a range, this needs to be separated into two separate columns
Why can't I apply text functions on strings into the file? For example: can't apply "Text To Columns" delimited by space on "Status Entry Date" column.
On a daily basis I open about 35 text files in Excel at one time, and then must responde to 35 dialog boxes:
"This file is not in a recognizable format" - I click OK "Delimited or Fixed Width (default)" - I change to Delimited "Delimiters listed with Tab as default" - I change to Space
I go through that 35 times. I don't save these files that I have opened, but once they are all open I paste each one in a separate worksheet in one workbook.
Can I at least change the defaults on these text boxes to Delimited, Space? That way I would just need to click on OK, then Finish for each file.
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.
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 ?
I was wondering if their is a simple macro to transpose data from rows to columns so I can export to a tab delimited file. This particular list is 5 lines underneath each other and then the next entry.
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
I attached a txt file of a report we get out of our accounting system. It is not tab deliminated, when I open it in excel, each whole row is contained in 1 cell instead of being broke up into columns. Im not sure how to convert it, im sure it possible using vb or something?
I have the following code (borrowed) which converts the current .xls worksheet to a tab-delimited .txt file. The problem is that i need to add a PIPE to the end of each row/record as well, so that the records would look something like this:
A|123| B|456|
currently there is no PIPE following the last character (3 or 6) and i am getting this:
A|123 B|456
I was hoping there would be a way to revise the VBA to add a PIPE at the end of each row/record.
I have the following code (borrowed) which converts the current .xls worksheet to a tab-delimited .txt file. The problem is that i need to add a PIPE to the end of each row/record as well, so that the records would look something like this:
A|123| B|456|
currently there is no PIPE following the last character (3 or 6) and i am getting this:
A|123 B|456
I was hoping there would be a way to revise the VBA to add a PIPE at the end of each row/record. Here's the ...
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.
{= 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.
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.
I would like to record/create a macros to open the file and paste 4 columns worth of data into another spreadsheet. Making sure the data is pasted in the correct column and doesn't overight data already in a tracker. Ie. paste it into the next available space.
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.
i have a problem in copying many columns to to rows that originally contains data, this is gonna be really complicated so here is wt i want, let's assume i have this table .....
I have a spreadsheet of 1000 rows on my spreadsheet and I am trying to convert them to 2 columns. Below is an example
Antidiarrheal AgentsBrand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume Antidyskinetics Brand or SeriesApplicationGeneric Drug NameStrengthTypeDosage FormContainer TypeVolume
I want it to look like this:
Antidiarrheal Agents Brand or Series Antidiarrheal Agents Application Antidiarrheal Agents Generic Drug Name Antidiarrheal Agents Strength Antidiarrheal Agents Type Antidiarrheal Agents Dosage Form Antidiarrheal Agents Container Type Antidiarrheal Agents Volume Antidyskinetics Brand or Series Antidyskinetics Application Antidyskinetics Generic Drug Name Antidyskinetics Strength Antidyskinetics Type Antidyskinetics Dosage Form Antidyskinetics Container Type Antidyskinetics Volume
Is there a formula or a suggested way of doing a macro to acheive this?
I have a spreadsheet that consists of columns of data and I need to covert it into rows depending on how the data is set up. In column A, there is a list of invoice numbers. Columns B, C, & D are as follows:
Column B: Sales Rep Name Column C: Sales Rep Number Column D: Sales Rep % Split
I want to have a single row for each unique invoice number. As an example, I have the following:
I have data which is in rows. I want them to be in 5 columns.
I am attaching the sample.
for some of the entries, there will be 6 rows: Company Name - 2nd row Address - 3 row. In this case, I want to combile them as one entry Separated by a comma & placed in one column Eg: For 3rd one in sample,
Haifa Marble & Tile 69 Garfield St Wanted Result: Haifa Marble & Tile, 69 Garfield St
If this is not possible, then suggest how to combine two columns to make it into one column separated by comma.
When I was using Excel 2000, there was an Excel add-in where I could highlight rows of information and then transpose these into columns of information. Since we have upgraded to Excel 2003, the same Excel add-in does not work and I have not been able to find a simple solution to transpose my information from a horizontal view to a vertical view or the reverse.
Or do I just have to move 53 columns (weeks) of 4 rows one cell at a time to 4 columns of 53 rows (weeks)?
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
I have a 45 page spreadsheet with over 3500 contacts and the data is currently listed in individual cells as seen below:
John Smith Director of Business Travel Sales ABC Hotel 1200 Market St. Philadelphia, PA 19107 Phone (215) 555-1234 Fax (215) 555-4321 jsmith@abchotels.com www.abchotels.com
I want to convert the each item [data] above into separate columns so I can then save it as a CSV file and then export th data into an email list; but I have no idea how do to this.
I have exported a text file from a universe database and need to import it into excel.
Below is an eg of one section. The text file contains approx 257 of these, all one below the other.
Between each set of dotted lines is one user profile (not data all shown in eg) .
Each one (user) is NOT the same amount of lines(rows) deep and only the dotted lines separate each user.
A simple import places all data into one very long column.
I need a way to place each user in there own column.
I have user names as column headings the labels of the values below as row headings. I cannot change this.
Manually entering the data took a very long time and i need to update this workbook regularly.
In the text file, " the variable......: value " (eg UserCode.....: XYZ) sets are all lined up so that using a text editor, with column mode, I can delete all the data headings/labels up to the space after the colon. This leaves me with 7000 rows of values (eg XYZ) one column wide and various numbers of rows deep per user.
Is there a way to import the data and seperate each user via the dotted lines or other so that each users data is contained in consecutive columns. 1 user profile per column.
The standard import utility allows for column placement (delimted, fixed width). Can this be done on rows, sort of flip it 90 degrees and use the dotted lines as column markers. Just guessing now. Any and all assistance would be most appreciated.
Another issue (not as important but still helpful) is that the "procudures barred" section of user profile can also be one row(line) or serveral. Problem is each Name...:Value set must be on one row(line) for everything to match in the work book.
Using custom macros in my text editor i moved all the 'proc. barred' data to a single row. Alot of manual editting was involved and any ideas on this area would also be muchly appreciated.
One EG user profile (not shown, but all the colons line up) ....
how to word it but if someone understands then please help. I have two excel data files namely Book1.xls & Book2.xls. Both files have different data in it. Both files contain macros. When these macros run the files become **FINALIZED** version.
Originally, I get the above files in my email as txt. attachments. I then move these two txt files to my desktop in a folder called Folder-1. Then I open these files as an Excel and save them.
Basically, I need to know if two txt files are sitting in a folder-1 on my desktop. What can I do or what can I clik that....those two text files get converted into excel automatically, including running that macro I talked about in the above paragrah.
To put it differently, if I have two txt files Book1.txt, Book2.txt in a folder, how can I automatically create an excel **FINALIZED**version which sits right next to their txt version.
i was stuck with some mass data which spans over many rows and columns. i am relatively new to excel and wanted to know how i can get this data arranged in a way where i can access all the data from a printable view. i have attached a file explaining basically what i want.
