I have a sheet with only one colomn, and about 200 rows, containing alot of 'garbage'.
Each few rows, I have a keyword ("REFDES=") and after this keyword I have a value ("R3254"). I need to extract only these values and to put them in one row, few colomns.
For example, if the sheet looks like this: ......
I wanna extract anyt text in a call that starts with LQ1 to a length of 10 characters to an empty cell, coma delimited.
View 14 Replies View RelatedI had VBA run a third-party program ("HYD") using data listed in my spreadsheet. HYD produces a textfile and I want to extract a number from that log (line 58, columns 6-10 to be exact).
I started with
Open "C:TempHYDLOG.TXT" For Input As #1
but I don't know how to have it skip down to line 58 or which function to use to extract the number I want. I've read through tons of help files and forums but none of them treat anything as simple as scrolling down to specific lines.
The entire subroutine up to that point is as follows: ....
I have a spreadsheet which is an extract from another application's DB, and it's just ugly in Excel. No nice neat columns etc. Data from some tables of the other application were just dumped into cells. Some of the data I need is scattered about the worksheet in various cells. There are no seperators that are common, no common length of text strings I could use to pull every X word etc.
The one common thread I am working with here, is the the data I need to extract ALL begins with the first 4 letters, and containers a total of 8 characters. For example:
A3 - The dogs collar had the name ABCD1234 on it
J9 - Today I found a dog collar at the store, and it read ABCD2345
c5 - It says ABCD3456
I'm trying to basically see if there is a way/function within excel that can go through all of the cells and extract anything that says ABCD? (and the 4 wildcards after it)
The Find all function finds them all, but trying to just extract the data ABCD? info.
i use this code to get the value from the cell that contains "Ink"., and i got the codes from reading other problems:
=IF(SEARCH("Ink",a1),LOOKUP(99^99,--("0"&MID(a1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},a1&"0123456789")),ROW($1:$10000)))),"")+0
like this in a1 -> Ink 253.00
and totally working! but the problem is if the word "ink" in the left of the value --> 253.00 ink and the result is #NA, is there any way that i can get the value whether the word Ink is in the left side or right side of the value?
also bothered why is it if the word is not "ink" in the cell and return -> #value since i put ("") in the last part of If function(value if false)?
Need to write a Macro to only extract numbers from text, characters and numbers fields. I would like the numbers to be extracted in column B, C & D. I am only interested in numbers that begins with 200's, 800's and 4500's. see the attached file.
View 3 Replies View RelatedI have been trying to work this out by looking at other posts (mostly concerning Binary Access) but can't figure it! The source text files I am using can vary in length from 4,000 characters to well over 100,000 characters. However the data I am looking for always starts 40 characters from the end of the file and is 10 characters long! I need my macro to pick out this data and store it as a string (so it can be added to an array and exported to a worksheet later)
View 2 Replies View RelatedI have a few hundred data/text files to analyze, some a few thousand lines deep. I would like to grab 5, just 5 numbers describing voltage levels. I know the preceding text for each of these in the cell and the following text as well. I just need to delete everything else and keep these 5 values in a column using a script or a macro, and do so for all these files in a specified folder.
This would be 3 weeks of work and the analysis needs to be done ASAP for my research.
Thousands of lines of ancillary data
Thousands of lines of ancillary data
Thousands of lines of ancillary data
Thousands of lines of ancillary data
"11/09/07 14:42:20 J13-50 and J6-9, PCU Resistance (XXXXXXX. Ohms) is within 1.000 to 3741."
Thousands of lines of ancillary data...........................
I am trying to find code that locates a workbook (file) in a specific directory, based on a keyword and stores the workbook location in a string to be opened later.
For example, find a workbook containing the word "ancaster" in it's file name (actual file name is "ancaster_summary_2009") in file path:
"C:My Documents" and store the filename and path in a string called "ancasterBook"
I'm looking to identify a keyword in a string and then replace that string with just the keyword.
The string is a product description. The keyword is a product group. The keyword can showup in any position in the string.
I can't post the actual data do to confidentiality requirements. but here's an example.
description
qty
price
keywords
code
green grapes bunch
1
2.5
[Code] .......
I need to conduct keyword searches in excel and color the keywords a different color without changing the font color of the entire cell. now, i found some code from another thread that does this, but i cant seem to get it to work with more than 2 keywords. i need fine-tuning the provided code to do what i need it to do.
[URL]
the VBA code i used was this:
[Code].....
I was playing around with the example, the Cat_Mouse.xlsm, and when i modified the 'myList' and 'myColor' arrays, it does not work.
Cat_Mouse.xlsm
For example, I modified the code to add the word bat:
[Code] .....
When i run the macros, the word "Bat" does not become colored red. interestingly enough, when i substitute the word "bat" for the word "hat" in the "myLIST array, the word "hat" does change to the red font.
I am looking to use this code to address keywords in my excel file by coloring all key words red and i have more than 10 key words.
I paste new data into a sheet of a monthly report I prepare. For this sheet, the # of data rows change (and is unpredictable) every month. I need the value inside a specific cell that dynamcially moves up and down based on the # of rows for that month (because it's below the rows of data).
So I made a formula to identify the exact cell # every month.
Example:
This month the exact cell is F255 in the "Refi" sheet.
So my formula in the "Summary" sheet cell A1 first finds the cell row # only (255) and since it's always column F, in B1 I have
VB:
="F" & (A1)
This outputs "F255" in B1, successfully identifying the target cell.
Now how do I write a formula in C1 to grab the value from whatever cell is named in B1. (For this month, the value in cell F255 from the "Refi" sheet)
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies View RelatedI'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.
For instance, in Sheet1:
Chicken
Cow
Donkey
Pig
[Code]....
So the above table would be the result i'm aiming for.
NOTE, its not different workbooks. I'm looking for sheet to sheet macro.
I've attached a file as well if someone wants to have a go at it. There are no codes in it.
I want to extract specific expense for my year 2009 work book. such as just rent with relevant data from all other expeses.
for example in my work book, I want to extract rental expense with relevant GST PST and Total for all three months. i attached a excel work book for broader idea.
I have decriptions in Col B on sheet1. I would like a macro to do the following
1) Extract descriptions that contain the following text to sheet2 (including Cols A:B)
sale, sales, fact assist , discount, DIC & rebates, F & I (for eg sale pine desk, fact assist pine desk, discount retail desk, F & I sales)
2) Extract descriptions that contain the following text to sheet3 (including Cols A:B)
COS, O/allowance (for eg COS pine desk, O/allowance,
I am trying to extract numbers after a specific text in a text, for eg :
abc SN 12345 xyz
edf SN No. 456 mno
As per above, i want to extract any numbers afters "SN". the numbers can be vary in digits i.e. it can be 3 digit numbers or 4 or 6. Also, at times there is some other text in between (like SN No.) numbers and search word (i.e. SN)
Any formula to get result as "12345" and "456".
Cell in excel containing large amount of text. Contains the following text one or more times (including the speech marks): "Provide Dwd/Dwl Number: DWD*****"
What I need to do is extract all of the DWD values from this cell, load into an array and dump them into another cell. It's the extraction bit that I need to sort out. In my mind the way that the code should work is:
Find all instances of 'dwd'
For each instance of 'dwd'
check the next/proceding character.
if the character is a space, check the next one
if the character is not a number or space, break and go to the next instance of 'dwd'
if the character is a number add it and the next 5 characters to a temp variable (varTemp)
loop
take the value of varTemp, prepend it with 'dwd' and add it to the running list/array
Loop
Output the full list of DWDs
I Need to loop through col W Used range except the last row which is totals or the first 4 rows which is Heading stuff, and if value > 0 post it to a column on the right based on the day code. ( 31 values 1 for each day)
Day code is in cell M1 and is an Integer
eg
Day 1 = Post to Col 101
Day 2 = Post to Col 102
etc...
I'm trying to extract some data from an online page, but I require a specific cell at an exact time, each day.
For example, I would like to acquire the main data from the following page: [URL]
into excel, but additionally, I require the ESU14 (Sep '14) Open cell at exactly 0700 BST. I am currently using the 'Data' - 'From Web' feature to scrape the necessary fields into Excel however I haven't found a way to acquire one of those cells, at exactly a certain time, every day, even when Excel et al aren't open.
I have data in several cells, and I want to extract only the information in the cell up to a /. For example "joesmith/shipping" I would like to have only joesmith in the cell next to it.
View 2 Replies View RelatedI want to extract data in table with this order C7:AA7
Sheet2 *ABCDEFGHIJKLMNOPQRSTUVWXYZAA1LetterNo
*************************2A5*************************3B15*************************4C3*************************5D2
*************************6***************************7**ABCDABCDABCABABBBBBBBBBBB
i want to extract the data from the table
Sheet2 ABCDE1Data 2ABSCar1Car3Car6Car13ADFCar2Car5Car3Car24ANDCar1Car1Car1Car35ADXCar6Car6Car5Car1
6 7 8 9Results10 Car1Data11 ABS12 ABS13 AND14 AND15 AND16 ADX17 18 Data19
Car2ADF20 ADF21 22 Data23 Car3ABS24 ADF25 AND26 27 Data28 Car5ADF29 ADX
I have 20 numbers from D3 to W3. Numbers range from 1 to 80. There are several rows with numbers.
I would like a macro to pic from each row specific numbers and copy them to EC3.
I've had a look at some examples of extracting unique values but it seems quite complex to me!
I have a list of customer names in column A, column B contains a material code (number format), column C contains the material information sheet (text & number format), column D contains the date that the material information sheet was sent (all column headers are in row 1).
Columns E, F & G follow the same format as above but for a different material code - this is repeated further in Columns H, I & J.
All of the material codes are different, but some material information sheets can be the same for some materials.
In column K I need to identify the unique material information sheet values for each customer, as some are duplicated.
Is this possible? There may be some blank values in the row i.e. a customer does not receive a certain material.
Perhaps just an easier way of organising the spreadsheet!
How can I extract mid section of the string which is always 5 characters long and is always followed by a period "." ?
My account strings are in 3 sections (but the third section isn't always used)
For example,
1.10210
1.22556.001
900101.56201
955261.54444.001
5566625.58886.957756
I was wondering if there is a formula that will search a cell for a word or other specific criteria then if it finds it, puts the requested data / word in the formula cell
Example:
If I have a list of vehicle descriptions all in different formats:
CAR1 1.4 SRI 3 door Hatch
CAR2 5 door saloon GSI 2.0 V8
2.2 CDX 5 door CAR3 Estate
CAR3 Estate 5 Door CDX
Say I want to know which ones are CDX varient I need the formula to look in the cell and return "CDX" or "YES"
I am using some code to loop through a folder and extract some data from a specific sheet.
Now say a user moves the file to another folder etc... The macro will pull up an error, now is there a way i can have it dynamic linked to the spreadsheet so no matter what folder it gets moved to,
My folder path will adjust to that or need to take another route?
What formula or function truncates a text field that is in the following consistent format:
number. name
ie. 3. Super Bella
and 150. Taoist
I wish to truncate the field so I have just the integer without the dot, space or word(s).
Tried Len, Left & Replace but they require a specific number of characters. Since the number can be 1 to 3 digits long, this doesn't work.
I have a set of task descriptions that I am attempting to trend on. Some of these (Column B) have the customer's name added to the description; others do not. I need to be able to make a list of task names (ColumnA), removing the name from the text string.
The formula I am using is [=LEFT(B3,FIND("for",B3)-2)].
The problem I am having is when the description does not contain the "for" built into the formula, I get "VALUE" error. What adjustment can I make to the formula to pull over the Description if the "for" does not appear in the text string?