Extract Multiple Pieces Of Text Between Two Strings
Feb 11, 2014
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:
Year, make, model, drive, part, name, phone, text, email, location, notes, stream, date/time, disclaimer
Here is an example of the text in column E:
Year: 2001 Make: Nasa Model: big spaceship Drive:
Automatic 2 Wheel Drive Spaceship:
4 12.7 Liter ----------------------------- Part: Hood -----------------------------
[Code] .........
The data in each field can be different lengths, and I would like to take pieces of text out of Column E, and put it in other columns in the row.
Each field has its own column, and I would like to copy the data for that field from the column containing all data to its respective column.
=MID(E1,SEARCH("Year:",E1)+5,SEARCH("Make:",E1)-SEARCH("object",E1)+5)
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‎
View 2 Replies
ADVERTISEMENT
Sep 4, 2009
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.
View 9 Replies
View Related
Apr 12, 2008
I have a column of data in the following format:
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.
View 8 Replies
View Related
Jun 26, 2008
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 Related
Jan 18, 2008
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).
View 2 Replies
View Related
Sep 28, 2008
i would like to be able to use a macro that will delete all columns in the sheet that do not contain one of 5 pieces of text in cells 1 to 20.
It is driving me mad, i have managed to find macros that delete empty columns, but they do not remove the columns that do not contain any of the 5 words. often the cells are empty down to row 5, when the data starts
View 6 Replies
View Related
Jul 1, 2006
I work in HR MI and am compiling an equality spreadsheet. Out of everyone who is on the list I want to see how many men and women we have which I have figured out using = SUMIF(GENDER,"MALE",NUMBER) but from that I want to say theres 50 male in total and out of that 30 are White and 20 are Indian. Is there anything I can add to this formula so it looks at more than two defined sets of information??
View 2 Replies
View Related
Mar 8, 2013
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.
View 1 Replies
View Related
Mar 11, 2014
I have a spreadsheet with 1,000 rows of data.
Each Cell in Column A has a different long text string.
I need to see which (if any) of 10 specific small text strings exist within each long text string. Depending on which small text string is found I want to return a 3 digit code. If no small text string is found I want to return "Not Found"
E.g.:
- Cell A2 contains "randomtext,randomtext,APPLE,randomntext"
- I want to see if Cell A2 contains any of the words APPLE, ORANGE, CARROT.
- I want to return "APP", "ORG", "CAR" or "Not Found"
Q: What is the most elegant way to accomplish this within a single formula that I could paste into each cell in Column B?
View 2 Replies
View Related
Feb 4, 2012
I want to search all the terms Column B within all the text phrases in Column A. I am ok if the words do not match the case sensitivity as I can address that myself.
If the exact phrase match is found, return the value True or False.
Column A
A1:Dog likes to run
A2: Cat Red-tummy-01
[Code]....
View 9 Replies
View Related
Aug 12, 2014
I want to search a longer text field for a shorter string that may be within it. However, I want to do this for multiple short strings.
For instance:
PhraseHidden word
Please search the text for the hidden message: Blue
The hidden word, Green, may be at any point
And the word may be different lengths like Yellow
I have a table of the hidden words (Blue, Green, Yellow, Pink)
I know I could use search to find one word, but is there a way to look for multiple words, and return the value of which one it finds? I have many hidden words (and the list is dynamic) so I don't want to just split into multiple searches.
View 5 Replies
View Related
Nov 19, 2009
I have been given a huge membership list. The field for the CITY also as the two letter state abbreviation (e.g., "Fremont, CA" instead of just "Fremont".). I want to be able to have the "CA" or "WA" or "NV" (etc) from the city field appear in a new STATE field. I successfully use the below statement to do this with the "CA" but I want a statement that will search for multiple strings (the other states).
Here is what works now: =IF(FIND(" CA",F2),"CA"). But I want to be able to add other state abbreviations to this.
View 4 Replies
View Related
Aug 20, 2013
I am trying to concatenate multiple strings of text where each may have composite or compound formatting. Is there any way to maintain the formatting? The Characters.Insert method won't work because the resulting string is greater than 255 characters. See an example below.
String 1: When you simply concatenate
String 2: these strings, Excel discards
String 3: formatting at the character level.
Required Result: When you simply concatenate these strings, Excel discards formatting at the character level.
View 5 Replies
View Related
Mar 7, 2014
I need a formula or array to perform the following in Excel 2010:
I have 2 worksheets "Master Report" and "Free text comments". The master report will be shaed with individuals but the Free Text comments worksheet will not.
sample feedback report.xlsx
In cells D2 downwards on the "Master Report" I want to show the individual feedback comments from the "Free Text comments" worksheet that have been made by other people for the individual named in cell A2 on the "Master Report" worksheet. So on James' master report it would show the 8 individual comments listed for him on the Free text comments" worksheet.
I have searched the internet and tried various combos of lookups, IFs, index and match but have not come across the right formula yet.
View 1 Replies
View Related
Apr 22, 2013
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
View 2 Replies
View Related
May 23, 2013
I have a spreadsheet that has relatively clean data, but at the end of every row is a long notes fields (often filled with several paragraphs of text). I'm trying to search inside each one of those notes to see if it contains certain boilerplate language / legalese / key words.
If I was just looking for one word, it would be easy -- I would write =IFERROR(IF(SEARCH("keyword",E2)>0,"Yes",""),""). That way, if the keyword is present, it returns "Yes", and if it's not present, it returns a blank.
What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return a "Yes".
So I could do something like =IFERROR(IF(or(SEARCH("keyword1",E2)>0,SEARCH("keyword2",E2)>0,SEARCH("keyword3",E2)>0,SEARCH("keyword4",E2)>0)),"Yes",""),""), but that seems horribly inefficient. Especially since my list of keywords is likely to change over time.
So what I want it to do is search each cell by simultaneous reference to an ever-changing table of keywords (call it [KeywordTable]). And I can't figure out how to do that. The search function is resisting all of my efforts to put multiple search values / a range of words inside of it.
To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [KeywordTable]", and if the answer is "Yes" return yes, and if the answer is no return no (or blanks).
View 7 Replies
View Related
Jun 11, 2014
In Column A I have the list of strings that I need to search. I want to count the number of occurrences of these strings inside these two text files: Sample1.txt and Sample2.txt. For example I have the string "DOG". I want to search and count the number of times this string appeared in Sample1.txt, and Sample2.txt
See below:
View 5 Replies
View Related
May 7, 2014
I have hundreds of records of customer info and I am trying to categorize the job titles so that I can conduct some analysis with a pivot table. My problem is that there is about 20 different ways and variations of "manager" so Instead of autofiltering for "mrg." "Manager" "marketing manager" ect. and then replacing the text manually and using the enter and fill process (to create some standardization to compare "manager" to "director" to "c-level" to "consultant", etc.) and repeating this process over and over again...
I would like to create a VBA that would search the column "job title" for multiple text strings at once and if the text string was true in the cell then the cell would be replaced by a new text string "Manager". I was thinking a series of if functions within one vba but I am not sure if this is possible.
View 8 Replies
View Related
Dec 20, 2012
I am looking for a way of creating the following conditioned concatenation.
I have two tables, let's call them "summary" and "detailed".
The "detailed" table is something like the following:
ID
VOL
001
01
001
05
[code]....
The "summary" table below gets info from the "detailed" table. The 'ID'is now unique. I'm looking for a formula on the 'VOL (concatenated)' column cells it should get all rows from the "detailed" table with the same ID and then concatenate the 'VOL' column results, comma separated:
ID (unique)
VOL (concatenated)
001
V01, V03, V05
002
V01, V04
003
V06
PS: I have people using this table with office 2003, so compatibility is necessary...
View 1 Replies
View Related
Dec 22, 2008
i have data in column A and i want extract to column B only the date from this data. Date start on diferent position.
ex:
col. A
1 text text 10.10.1998 text
2 text10.12.2001
3 10.12.2004
4 10.12.2005 text text
i want this result
col. B
1 10.10.1998
2 10.12.2001
3 10.12.2004
4 10.12.2005
i try solution from other thread, but it doesnt works for me.
View 10 Replies
View Related
Mar 7, 2009
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.
View 9 Replies
View Related
Aug 17, 2008
I have about 50 - 60 text files and I need to find certain values, namely Name, Account No, total amount under Portfolio Value in the text file and copy these values to a worksheet under column: Name, Account and Portfolio Value respectively. All new entries are paste one row below the existing data.
Some of the text files may not have any data in it or may not have the Portfolio Value as the monthly transactions may occupied 2 or 3 text files. Some text files may have 2 Name in it and there will be 2 Portfolio Values in it which I will need both to be copy to the worksheet.
I have attached both the Excel file and the dummy txt file.
View 3 Replies
View Related
Oct 8, 2007
how to extract names from a worksheet and put them in another by alphabetical order?
There are 6 “could be more” blocks of data on Monday’s Worksheet, not all rows of each block will be filled. The idea is put the data from Monday WS in List WS without spaces and by alphabetical order. Because my English is not good enough, I attached a file with what I need.
View 2 Replies
View Related
Oct 22, 2012
black.blue.red.yellow.green.white
I'd like to extract just "blue.red.yellow.green" to the following spreadsheet column, keeping the intermediate dots, but getting rid of the first/last words and their succeeding or preceding dots.
I've tried using RIGHT, LEFT, MID formulas unsuccessfully
View 3 Replies
View Related
Oct 3, 2008
I 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...........................
View 9 Replies
View Related
Feb 28, 2013
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?
View 6 Replies
View Related
Sep 27, 2006
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.
View 9 Replies
View Related
Dec 7, 2012
I have a large .txt file that I would like to edit using VBA. What I would like to do is to find each of the following text strings in the text file :
De:
De :
Envoye
A:
Objet:
If the character right before those text strings is not a carriage return (chr(13) then add a carriage return.
I would like the code to read each line in the text file and execute the task mentioned above.
View 9 Replies
View Related
Apr 23, 2007
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
View 9 Replies
View Related
Jan 30, 2009
I'm trying to extract pieces of a part number and place each section in another cell. It's kind of like a text to columns, but in a formula and removing part of the data. This is what I'm starting with: Cell 3A: HB149E.505
I'm trying to figure out a formula that will fill the following which are currently blank:
Cell 3A: HB149
Cell 3B: .505
Cell 3A will always have 5 digits prior to the decimal and 3 digits afterward so that is not a problem.
View 2 Replies
View Related
