Hardware (somethingsmall) otherinfo
Hardware ( somethingdisplaced)
Blackbox item (somethingelse) other info
Service(aservice)
etc
I need to extract the data within the brackets allowing for additional spaces for use in a second query which will search for each individual line item in turn.
For some reason the query does not like searching for the raw data in it's current format so I'm trying to extract the key information for searching instead.
I can do it in excel by:
(Assuming data is in cell V36)
'in colum x
=FIND("(",V36)
'in column y
=FIND(")",V36)
'in column z
=MID(V36,X36+1,Y36-(X36+2))
in column aa
=IF(LEFT(Z36,1)=" ", MID(Z36,2,20),Z36)
I have tried doing this cleverly in VBA and failed and then ended up trying to enter this code in the cells within the VBA code and do it that way but I'm having difficulty in doing this also.
I have the following issue. I have a list of strings. Each string contains certain characters that are exactly the same for all strings, some characters are different making the string longer (in some cases). What I need to do is extract some combination of characters from each string. The strings look like this:
AB & CDE & FG & I mmmm yyyy.HIJK AB & CDE & FG & II mmmm yyyy.HIJK
The part "AB & CDE & FG & " (incl. spaces) is the same for each string. The next part contains a roman count from I to VII, causing the length of each string to vary. The "mmmm" part contains the current month spelled in full e.g. December. This part differs as well, for each string. The "yyyy" part contains the year in four digits, e.g. 2007. The part after the dot is the same for each string again.
What I need to do: - is to extract the month and assign it to a new string - extract the year and assign it to a new string - extract the roman number, translate it to a normal number (II -> 2) and assign to a string (or integer).
An original text string entry appearing in an Excel cell would be:
"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"
I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.
Another example would be (very different from the first):
"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"
You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.
In column E of row 1 I have one column with a long line of text containing information in that should be separated into cells for the following fields:
The formula above was inspired by an "extract text between two strings" post, but will not work.
I tried to make a formula that would extract everything between the strings "Notes:" and "Team Stream:" or for example, so I could get the result "Thank you for using our excel forum! thesmartestpeopleever.com" or "Year" and "Make" so I could get the result "2001" if using the example above. I was not able to do it.
I have attached a sample photo and an excel file.
How do I separate/extract each columns data to the appropriate column?
Attached Image : excel string pic.jpg‎
Attached File : excel string formula conundrum.xlsx‎
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.
I have the following information in a cell. I want to get the exchange:ticker which is inside the brackets to be returned (coma delimited). How can I do this? One thing to note here that the number of companies in the cell can vary
Banco Bilbao Vizcaya Argentaria (CATS:BBVA); Banco Santander, S.A. (CATS:SAN); Banesto Banco Espanol de Credito SA (CATS:BTO); Banco Popular Espanol SA (CATS:POP); Caja de Ahorros y Monte de Piedad de Madrid; La Caixa Group, Asset Management Arm
I am importing into Excel a list of contacts from a txt file so I can make a mailing label database. I am able to seperate out the various parts of the address just fine but here's where it gets tricky.
Below is a example of the text I am importing: Mr Dow Jones, 600 DIXON ROAD, TORONTO, ON M9W 1J1, CANADA (1-416-6757611)
I need Excel to look at the cell containing the country name and: 1st. see if there is a phone number present (this is the numbers between the () and if there is - place the phone number in Cell F1 and place the Country in Cell E1 but strip out both the () and the phone number. If there is no phone number present (and therefore no () I need Excel to go ahead and place the country name in E1
I try to write the function that gets some "ref" and returns appropriate item. For example: if I give to function "C3", it returns "AAA" if I give to function "R18", it returns "BBB" (cause it between R15 to R26) if I give to function "R9", it returns "BBB" also.
Im new to Macros. Im trying to find a string of text assign that to be the top of the data and then find a different string and assign that to be the bottom of the data. Then run a loop whilst inside that data range. Am i going about it the right way? Attached is a sample data file.
I receive data from different sources and one column is usually in upper case, but I prefer to use proper (title) case. The problem is that the data frequently contains text strings that should remain in upper case or should combine upper and lower case letters. Examples follow:
Data received: COROLLA AE90, AE92 COROLLA SX, GTI
Data should look like: Corolla AE90, AE92 Corolla SX, GTi
Note that 'GTi' is upper case 'GT' and lower case 'i'.
I've set up a spreadsheet 'CaseConverter.xls' as a look-up, with a range ('SpecialCase' - A2:A65536) featuring names, codes and acronyms represented correctly. Is it possible to use a macro to run Excel's global replace to search for upper case strings in one spreadsheet and replace with the correct strings from the other spreadsheet? The macro would have to loop through the entire 'SpecialCase' range and look in the supplied spreadsheet for versions of the data in the special case range without being case-specific. The supplied spreadsheet (see attached for example data) is called 'Application.xls'. Column B is what I'm trying to reach.
I am very comfortable with VBA but I usually avoid using formulas if at all possible (unfortunately, I have, formulas are unavoidable (by request).. so formulas it is)
I will try to explain
Column A is a list of dates (ever expanding) Column B is a list of names ( multiple entries of various names but aligned with various dates) Column H is a list of names (each possible name which can appear in column B, no duplicates)
Cell I1 = A Date Cell J1 = A Date (forming a date range)
Basically what I need to do is:
= COUNTIF(B:B,H2) - but only count it if it is withing the date range (>=I1 & <=J1)
I have tried wrapping it in an IF statement but I can't seem to get it to work.
I'm trying to build a formula that counts the amount of cells within a range that contain multiple strings of text within the same cell. I only know how to build a formula that snags cells that contain 1 but not 2 different ones within the same cell. For example: I want to count cells if they have the word BALL and STICK somewhere in the cell....see three cell examples below
gameballnetstick ballgame stick ball tenballs green stick
Of the three examples: it would only count cells: gameballnetstick and stickball
I have a small problem that I may be able to deal with in another way, but if all else fails then I need some assistance.
I have this formula: =AND(N3<=$U$1,MOD($U$1,N3)=0)
But unfortunately there will be a time when cell U1 will have the contents of perhaps 12(1). Is there any way to get the formula to ignore the brackets and the figures in the brackets?
How would I go about counting the number of times TA shows within strings of text in a range within a sheet. Example: TA,MH in cell A2, CB,TA in cell C40, ES,TA in cell Q19. Result would be 3. Ideally, I'd like the formula to reference a cell that has TA as the look up data such as in A1 I'd have TA.
I have a spreadsheet with 900 rows. All of the rows hold different information. The first column has contents such as this:
This is the text (please remove me) This is some more text (I need to be removed too)
I want to remove from every cell, everything in the brackets - so everything from the first open brackets to the end of the cell contents). Every cell is a different length and the information within the brackets is different.
I have this formula that extracts numbers from alphanumeric strings.
{=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$100),1)),0),COUNT(1*MID(A1,ROW($1:$100),1)))} However this extracts only the 1st instance of the numbers
In a string like 123avfbsdf4556.. it'll extract only 123.
My questions are the following:
1. Is there a way that i could get the result as 1234556
2. A way which refers to a cell where I put in a number and it'll extract those many number instances. In the above example, if I put the number as 1, it'll extract 123. If I put the number as 2, it'll extract 4556 and so on.
I guess this would require some modifications to the Match function so that it does not look at only the 1st instance.
I am importing data into excel from another application. I place the data on a sheet and then use macros to re-arrage the data to another sheet. One column of cells gets populated with strings of the following format <text1>(<text2>) I would like to extract <text2> and place it in a cell on another sheet. the length of text1 and text2 varies.
A1: The War of Art by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook A2: Do the Work by Steven Pressfield (Apr 20, 2011) - Kindle eBook A3: How to Get the Raise You Want in 90 Days or Less: A Step-by-step Plan for Making It Happen by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook (Note: This cell (A3) have "by" 2 times)
I want to extract text from RIGHT till the word "by"
So the result must be: B1: by Steven Pressfield and Shawn Coyne (Nov 11, 2011) - Kindle eBook B2: by Steven Pressfield (Apr 20, 2011) - Kindle eBook B3: by Kathy M. Barnes and Robyn Feldberg (Jan 30, 2009) - Kindle eBook
I am trying to categorize a list of words based on the first letter.
For example:
[Code] ......
I want to see if the letter 'D' in 'Data' occurs in a range from A-G, if so display "A-G" in cell B, else if the first letter occurs from H-M, display H-N in cell B else if the first letter occurs from N-R, display N-R in cell B else display S-Z in cell B.
how to Chk the text string in particular cell, compare it with a super set column and get the full from of the text string from another corresponsing column and the output will be corresponsing full form of the chked text string?
How do I use an Excel formula to find which (if any) multiple sets, each of up to 50 words, exist in a series of rows of a spreadsheet - if set A has one or more words found in a searched cell.
A positive result will return a specific value in the designated result cell. If none of the words in Set A is found in the searched cell, the formulae will repeat the test for the words in Set B, and so on.
After all 50 sets of words have been tested, the formula will move to the next cell in the searched column.
New words will be added to the sets of words continually as required.
Multiple words within sets are included in double quotes. Within each set of words there will be some n-tuples of words (i.e. 24 adjacent words) that contain one or more of the words in the set, but for which the formula will be required to return a negative result. Example: Set A = word 1, word2, word 3, "word1 word2 word3". (The words within a set could also be each entered in separate columns, as opposed to all included in a single cell.) The single column of text to be searched is about 10,000 rows.
I am wanting to use the above in a spreadsheet that contains data downloaded from a series of bank accounts to automatically allocate items of expenditure to one of 20 or so different categories of expenditure.
The formula will search the description field to find words that are used in the in the downloaded files from the various accounts to describe each transaction.
If a word describing travel expenditure (e.g. hotel, "holiday inn" but not "holiday travel") is found in the description of an expenditure item - the item cost will be allocated to the TRAVEL EXPENDITURE column, which is one of 20 or so different categories of expenditure.
Happy to consider a different solution if the task can be done better a different way.
Tried using a combination of INDEX/SEARCH/IF in Excel, but was not able to get a correct result. PS I am using Excel 2011 for Mac - which does not allow macros, so the solution needs to be entirely formula based.
What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells
The data I require is :-
(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson
(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)
(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting
See examples 1 & 2 below
Example 1
CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting
Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before
Example 2
CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers
Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.
As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text
Different formula in different cells give me some text as output, to format this. I would like to copy and paste in one cell, so that I can easily then register a macro on top to wrap the text in one big cell.
I have a column filled with names per row. I wanted to standardize it by using the following format.
The comma after the last name is always an indicator to me that it is the last name.
<First Name> <Middle Initial(if any)> <Last Name>
My actual records contains the following:
BIMBO EPPING - OK AHMAD SADICK - OK Salana, Laura R - need to be arranged to > Laura R Salana Cantos, Rhoda Grace J - need to be arranged to > Rhoda Grace J Cantos
Can the below be achieved with any formulae or macro -
I have 2 worksheets (sheet1 & sheet2). In sheet2, i have some colors in column A (e.g. "red" in A1, "blue" in A2, "green" in A3.
In sheet1, i have some text strings in column A, e.g.
A1: I love red color A2: my pen in blue A3: Green grass A4: Orange juice A5: I like red and blue
What i want in corresponding cells in column B, is the name of the color which is in column A. so my output should be
B1: red B2: blue B3: green B4: "NIL" (any error message) B5: red (even though it contains 2 color, i am happy to show any one of the colors it contains)