one="dog"
two="cat"
three="bug"
four="bird"
I have a 5th variable with a sentence in it for example
Sen=”The bug and bird are in the park”
What I need to know is which of the variables content appears in the variable Sen first and what position it is. In this case it would be variable three position 5.
I have a parts list which came from one source in the format below. First column is the name of a product, the following columns show what components and what quantity go into the product:
Small Fruit Basket Apples 2 Oranges 3 Bananas 4
Large Fruit Basket Pears 5 Peaches 6 Mangos 2
The format I need it to be in is below, but I don't know how to go about it or if it's even possible:
Small Fruit Basket Apples 2
Small Fruit Basket Oranges 3
Small Fruit Basket Bananas 4
Large Fruit Basket Pears 5
Large Fruit Basket Peaches 6
Large Fruit Basket Mangos 2
I have arrived at the following in order to find the 5th "bar" in the data (samples below) :-
=SEARCH("|",$M127,SEARCH("|",$M127,SEARCH("|",$M127,SEARCH("|",$M127,SEARCH("|",$M127,1)+1)+1)+1)+1)
Samples:-
STR|6|L||396|3| STR|4|L|114|396|3| NHF||||1463|175| RND|4|H|111|2352|49|
Is it possible to reduce the repetition within the formula?
I'm looking to set a limit to the parse though.
For example:
Data in a cell:
Something x2345 is good
Anyone watching x56784 Yay! 345!
using:
macro that will parse data that has been pasted in to Excel from a text document. Once the macro is executed the pasted data will be sent to a separate spreadsheet and parsed horizontally with the data residing in is respective cell.
Here is an example of my data from text:
FIRST: Frank
MIDDLE: N.
LAST: Stien
ADDRESS: APT14
STREET: Halloween
ZIP CODE: 123456
CITY: Scaryville
STATE: Scary Ya
COUNTRY: Transilvania
COMMENT: N/A
Here is an example of my desired results:
A2:Frank, B2:N, C2:Stien, D2:APT14, E2:Halloween, F2:123456, G2:Scaryville, H2:ScaryYa, I2: Transilvania, J2:N/A
The data is always pasted in A2 on the first sheet, and varies somewhat in length but the format is always the same.
I have been given a set of data from one of our clients with company and address information in a Microsoft Word document. The data is not formatted into fields delimited by either a comma or tab and none of the fields has a heading of any sort. The data flows vertically rather than horizontally. I have imported the data into Excel in the hopes of being able to break out the data into an Excel or csv format in which the data can be imported into a Filemaker Pro database. Its format is the following:
Company Name (cell A:1)
Address (cell A:2)
City (cell A:3)
State (cell A:4)
Zip (cell A:5)
telephone (cell A:6)
I have tried recording a macro that moves the data from cell A:1 into cell B:1, then cell A:7 into cell B:2, etc. But have failed to get a macro to work that runs through all of the data in column A:A. The data is consistent - that is, there are only the 6 rows of data. Is it possible to write a code that moves the data from cell A:1 (the company name) into cell B:1, then skips 6 rows to cell A:7 (the next instance of a company name) and moves cell A:7 into cell B:2. The same procedure would then be run on the address, city, zip, and telephone.
Here is an example of some data I need to parse.
IN: CS, Test 2A-1, CBL, PHL, South Penn I/L, Loc P10, 49C-Loc P11B, AP 1.5
That code that I have so far:
select t.description,
REGEXP_SUBSTR(t.DESCRIPTION, '[^,]+', 1, 1) col_1,
REGEXP_SUBSTR(t.DESCRIPTION, '[^,]+', 1, 2) col_2,
REGEXP_SUBSTR(t.DESCRIPTION, '[^,]+', 1, 3) col_3,
[Code] .......
I have 2 problems:
1st problem is that for COL 7, all i want in there is the "49C", I'm currently getting " 49C-Loc P11B".
My ultimate goal is to get any number that is before the C and the C itself. The number before the C could be 1 to 3 digits.
2nd problem is Column 9. That snipit of data will always start with an "A", have another alpha, then a space, then a number. I'm trying to just get the number.
In general it looks like this: All in one column
flight 1.1
flight 1.2 paid $50 owes $100
flight 1.5 paid $80
pass paid $125
ground 1.2 flight .9
checkout flight 1.2 paid $76
I need to extract the numbers with a $ sign into a separate column. to look like this:
flight 1.1
flight 1.2 paid $50 owes $100...........50..........100
flight 1.5 paid $80...........................80
pass paid $125...............................125
ground 1.2 flight .9
checkout flight 1.2 paid $76...............76
.............. representing separate columns
I have a spreadsheet that gathers information from multiple cells and strings the data into a long string - each piece of data broken by a comma in the string.
In another spreadsheet, I copy this data string into column A on sheet 1 - starting on row 2 and down.
On sheet 2 - and this is where I need help - I would like to have a macro that will take each data string on Sheet 1 column A and parse it out into multiple columns but the same row on Sheet 2.
ColumnA Row2 Sheet1 -> changing to many columns on Sheet2 but still on the same row.
The data string always represents the same number of data components - its just a matter of breaking it out and placing each data piece into its own column.
a VBA code for PARSING and EXPORTING data to multiple excel workbooks based upon unique entries in column 'N" only for those rows for which column Y value is "ACTIVE".
I am currently using a code which allows me to export data based upon unique values of column N but how to weave in an additional pre-filter criteria of Column Y value being "ACTIVE".
Sheet4
ABCDE1Source DataDesired Result
Include these extensions: Exclude these extensions:2OLD.FILENAME.rar.htmNEW.FILENAME.rar
.part*.rar.htm3OLD.FILENAME.part01.rarNEW.FILENAME.part01.rar
.rar.html4OLD.FILENAME.part02.rar.html.htmNEW.FILENAME.part02.rar
.avi
5OLD.FILENAME.txtNEW.FILENAME.txt
.zip
6OLD.FILENAME.zip.htmNEW.FILENAME.zip
.txt
7OLD.FILENAME.aviNEW.FILENAME.avi
.7z.*
8OLD.FILENAME.7z.01.htmNEW.FILENAME.7z.01
9OLD.FILENAME.7z.002NEW.FILENAME.7z.002
I'm looking for a formula which can take my original data (column A) and "transform" it into what you see in column B.
I am transferring large amounts of files from various servers to my ftp and back out again. My original data are url filenames which of course, are named differently and not how you see it in my example.
The filenames themselves are preceeded by website names and varoius folder/path names. I edit the links to include various Usernames and Passwords and at the same time, rename the original filenames. Then I transfer the files to my ftp.
Most of the files have a ".part*.rar" extension at the end, so my formula has only included this scenario. Rather than manually editing my formula everytime a different extension is present (about 20-30% of the time), I would like to find a way to automatically look at a table/list of possible extensions (D2:D7) which are to be recognized for the sake of finding the end of the filename itself, so that I can rename (SUBSTITUTE) the original filename to that of my choice (cell D13) and of course, also include the original extension(s) of the source data (keeping this is important, especially in the cases where ".part*.rar's" are present). The list of extensions in cells E2:E7 are to be excluded from the new filename.
My present formula works fine for the editing of the username and passwords for various servers and for finding the beginning of the filename in the original url. It also works fine for finding the end of the filename (for the sake of renaming it), but only in cases where a .part*.rar" is present.
100% Bran Nabisco 70 130 10 5 6
AlI-Bran Kellogg 70 260 9 7 5
All-Bran w/Extra Fiber Kellogg 50 140 14 8 0
I am having trouble parsing the numeric data from the following table into separate columns. The five different numbers (ex. 70, 130,10,5,6 in the first row) represent nutritional info about the product. As you can see, the number length varies for the different categories. How to get these 5 numbers into their own columns using LEFT, RIGHT, MID, FIND, etc. functions.
I have a text file which is attached as "rawdata". It contains records of something (let's call it temperature) at different times on different days. My goal is to display a graph of temperature versus time so that I can visually analyze trends. I have hundreds of these files, all of different lengths. it is very important that I automate this process as much as possible.
Detail: (Here I describe what I have done so far; if this is inefficient or unnecessary, feel free to tell me) I open Excel 2010, click File, Open, and select the file that I want to parse. It is a TXT file, so the Text Import Wizard comes up. For step one, I select Fixed Width. I select File Origin: MS-DOS (PC-8). On step 2 of the wizard, I create column break lines to place all dates in the far left column. The next column contains the first column of numbers before the first dash (-). The next column contains only the dash - I will later select "ignore this column" to eliminate them. The next column contains the time stamps. I continue adding column breaks in the wizard until all of the data are parsed into columns in the same manner.
In step 3, I format the first column as "date (DMY)". The columns with the dashes I select "do not import". Everything else is "general". I click "finish", and the resultant workbook is attached, called "import".
Now, as to what I want to do: I want to display the "temperatures" as a graph vs a date/time axis. The reason I find this difficult is because the temperatures and times are not in neat columns, but are in 4 columns that go in a left-to-right and top-to-bottom progression and are broken up every few lines. (I am interested only in numbers that are displayed immediately to the left of a time-stamp. Therefore, the "record #"s should be ignored. We can delete the rows that say "record #" if can be done automatically.)
Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...
I have the
I am trying to figure out how to parse some strings. Example of strings
View 2 Replies View RelatedI am trying to parse employee names into seperate string variables in order to display surname, first name and middle name in seperate fields on a form.
The format of the full name is "Surname, First Name, Middle Name (If any)
I have managed to deal with surname and firstname but I am stumped on middle name. Not all employees will have a middle name but for those who do, I need to parse it.
I have a text file that has data at various time steps say 0,1,2,3 etc. In each loop, the output data is recoreded that contains numbers and strings. I want to parse certain info from this text file.
Say I am interested in reading the data for very last time step, so when I open the text file for reading, I want to move down to the Time Step 3 line. Then I want to read the text line by line, any time I encounter the word WIND, I want to skip five spaces and pick up the number that appears there. I want to paste this value to say cell A1 in a sheet called ParsedData. I want to continue reading till the end while picking all such values and filling the ParsedData sheet A2, A3, A4 ... cells till the end.
if i have to search for some special character in a string and i use Instr function then which of the following is the correct way. Lets say i have to search for "****" in the string
(1)
instr(myString,"****")
(2)
instr(myString,"****")
I want to create a vba code in excel to select rows and upload into an external application called siebel. i can take care of the later part but I am not sure how to select the records and do the upload. In my case I would like to select records from the 17th row until the EOF..
Also pls suggest which is a better option
1) Selecting first row, upload it and then select the next row and upload (OR)
2) Select all the records at a time and dump the data..
I am attempting to automatically (once a week) pull down the data from this site (http://www.dot.ca.gov/hq/esc/oe/plan...bidsum_csv.php) which is posted in a csv and parse it. While this may seem to be a simple process, the people who posted the CSV did not take into account that there are dollar values in the data in the millions and billions. As a result, a normal comma parse does not work since it splits the values.
View 5 Replies View RelatedIt's been a long time, but I use to parse a string in C.
How do you strip off the long directory path of a string to just the filename and extension?
String Example:
S:2006 jobs602f EID Bass Lake Recycle BPSConstructionSubmittal Review16940-Instrumentation602f-16940-01.rev.doc
Wanted result:
0602f-16940-01.rev.doc
I'm creating a table to store a large amount of data that will be continously updated over time. The table however will be viewed from a website, not as an Excel document, and I found a great add-in that generates small, clean HTML code to that end.
I'd like to do something else a little more tricky however ( for me ). I'd like every item in the first column to automatically hyperlink to a predesignated URL, and then parse the contents of that cell onto the end of the web address. If the cell contained multiple words, it would parse "+" between each word to create the proper address. The hyperlinks will be fetching database information from an exterior website.
For example, Column1 Row1 might say "Dog", so a hyperlink would be generated to [url] Column1 Row2 might say "Red Corvette", so a hyperlink would be generated to [url]
How can Excel input automatically the "total price" in the first table after I enter "type", "width" and "height" and looking up the other reference tables (below). The referenced tables will have fixed width and height but in the input table will be entered any number within the minimum and maximum range. So the idea is to get the price of the closest highest combination of "width" and "height" on the referenced tables and apply the price:
Input table (I will input "type" by drop down menu, and width and height)
type
width
height
total price
[Code].....
I need some type of vba or macro that can extract text in between html tags from a list of URL's.
For example
I have a list of urls in column A
Columns B,C,D,E - are where different types of information from the website will go.
So B contains values of text between a <title> tag </title>
C will contain values of text between a <object> tag </object>
and so forth ....
I would like to parse the below :
Say this appears in A column , I want to parse all the characters appearing after the numbers (i.e., 8.625% /5.875%/6.125% ) in B column - Any excel formula ??
Alere Inc. 8.625% October 1, 2018
Delphi Corporation 5.875% May 15, 2019
Delphi Corporation 6.125% May 15, 2021
So B should column like below
8.625% October 1, 2018
5.875% May 15, 2019
6.125% May 15, 2021
I have the attached spreadsheet that has 176K of addresses. The cell contains data that is actually Address Line 1 and Address Line 2. I need to separate these. I believe there is an unprintable character that separates most of them -- maybe a CRLF or something like that.
See the attachment for examples ... Cust Address with unprintable characters.xlsx
Column M:
##/##/#### | Variable Length Text-####
Example:
01/06/2014 | Daniel Trimble-4048
I need to parse out the different parts of Column M.
In Column R -- "Close Date", I'm successfully using:
=LEFT(M2,FIND(" | ",M2)-1)
...to extract the close date of the donation.
In Column S, I want to list the donor name--which is all of the text after " | ", and before the "-".
I don't need anything after the hyphen, and fortunately in this data, no one's name has a hyphen in it.
The Close Date is working fine for the LEFT and FIND functions, but for the life of me, I can't seem to get MID to work for the variable-length text. The text will always start in the same position -- 14, as the date and delimiter are standardized. And the last 5 characters of the text are not variable in length, so they can be cut out completely.
How do I use MID to extract everything starting at position 14, and stopping 5 characters short of the end of the text?
I have an address field that is concatenated as such that the city name is attached to the end of the street name as follows:
4 Example WayOakland, CA 94601
I have over 200 of these with different addresses and cities in my workbook so it's not always the same length. How can I grab the city out of this string? Is there a way to key on the capital letter of the city? Or is there another way?
I am trying to parse a section of html into excel but I don't know how to parse into a new row with each pattern rotation. I can do this both in Word and notepad++, but I can't find the same function of replace-with-line-break in excel's find-and-replace box.
Original html extract:
"shb":{"landline":"£1","mobile":"£1","text":"8p","mms":"24p"},"af":{"landline":"18p","mobile":"18p","text":"8p","mms":"24p"},"ak":{"landline":"3p","mobile":"3p","text":"8p","mms":"24p"},"al":{"landline":"10p","mobile":"25p","text":"8p","mms":"24p"},"dz":{"landline":"5p","mobile":"27p","text":"8p","mms":"24p"},
Results I need:
Capture.PNG
There is only one line as such in the entire page, so I don't need to loop through "blocks" of html codes with multiple lines.
I have had a look around and not sure what would be a good way to tackle this. RegEx (how to do it in excel macro)? HTML tags parsing (but these are not strictly tags)? I need to extract the correct section from page source as well, but I imagine this shouldn't be too hard. The url to the page I am extracting from is [URL] ........
to separate a very large excel file into multiple files based on what's in a single column.
I am an intern at my district school board and I'd love to know if there is a way to do this.
In the attached file the first tab is an example of some of the information I have in my file. My file is thousands of rows larger. The next few tabs are examples of new files (not just new tabs) That I would like separated and saved somewhere on my hard drive.
Notice the files are split up based on the student.
Is there a way to parse out this information into new files? The file is huge and I need it to do this automatically. If it isn't possible to save new files, is it possible to parse out the students into new tabs? But I have too many students I think. How many Sheet Tabs can you have in 1 file?