Extracting Text From Text String
Mar 17, 2008
I have a meeting name in column A that also includes a meeting #. The meeting number is always enclosed in parentheses and is at the end of the meeting name. There could also be information enclosed in parenthesis in name. I want to extract just the meeting # from the name. Also, the length of the meeting number could change. Here is an example of a 3 meeting names:
Example 1: March Madness (FY2008) Boston (8765) I would want just 8765 to be extracted
Example 2: Year End Planning Audit (11567) I would want just 11567 to be extracted
How can I extract just that the meeting number from these meeting names.
View 9 Replies
ADVERTISEMENT
Mar 29, 2013
I'm pulling phone numbers out of text strings. There is text string ("Office:") indicating that the number following is an office number (the number that I need). MID and FIND take care of this. There are sometimes two instances of office numbers in single text string, so adding a second column using the third argument of FIND lets me start another search past the first instance of an office number. We have a bunch of office numbers with the qualifier "(Text)" after the original "Office" but before the phone number. How do I catch these ones?
View 8 Replies
View Related
Sep 17, 2013
Date: 17/09/2013 KO: 19:45 Ref: B Malone Att: 7,574 extraction of text from the above text string which is say in A1. What l need is for Date to go in A2, KO in B2, Ref in C2, & Att in D2. I'm using Excel 2003
View 7 Replies
View Related
May 15, 2009
I have a column of names, I need to extract the last word
Simon Greaves
Martin John Mackay
Miss E Hoddle
Bob W Blanchflower
Sarah White
D Gascoigne
H G Gomes
Should read:
Greaves
Mackay
Hoddle
Blanchflower
White
Gascoigne
Gomes
View 7 Replies
View Related
May 11, 2007
I need to extract the text from a string that is always less the last three digits. For example if A1 is abcdefg I always have to take off "efg" and extract the rest of it to the left but the length of the string can vary. I can be dirty and record a macro where I find the length, subtract 3 and then using the LEFT worksheet function can get my result, but when I come to try to convert that to VBA. What is the VBA equivalent of LEFT worksheet function and how would I go about using LEN in conjuntion with subtracting 3 from it?
View 2 Replies
View Related
Aug 20, 2014
If I have a cell that contains a sentence and part of a date, for example "delivery due 01/09", I would like a formula that reads that cell, identifies the 01/09 bit and returns 01/09/2014.
The only trouble is that the date would always be variable, so it's more a case of "delivery due #/#"... if that's even possible.
View 9 Replies
View Related
Sep 8, 2009
I have a few hundred rows of text in the fomat below: 1.23456 xxxxxxxxxxxxxxxxxxxx. The “x’s” represent text which is unique to each row. what the formula I need to extract the number (1.23456) at the start of the string? To complicate things the number may be reported to any number of decimal places, so the formula needs to be able to extract the first block of digits at the start of each row and report it as a number that can be used in calculations.
View 2 Replies
View Related
Sep 22, 2009
I have a column of data in the following format:
261D 31/12/2008
There is also a single space after the end of the date.
Can someone please show me a formula to extract the date from each cell in the column? This is slightly complicated by the fact that the number of days (“D”) could consist of 1, 2, 3 or 4 characters, and the date could also be of variable length (e.g. 1/1/2008 vs 31/05/2008)
View 6 Replies
View Related
Sep 11, 2013
I have random text strings which I need to extract the last part from in Excel.
The text string will be made up of code, but the word I need is always to the right of a prefix />
So for instance in A1 I have 76ruihfo:;.>6">/apple
I would want to extract "apple" from the string above into B1.
View 3 Replies
View Related
Sep 28, 2005
is it possilble to extract integers from a text string within a cell?
Eg.
Cell A1 - "ABC 123"
I want to extract the "123" from cell A1.
I have many text strings from which I need to extract the integers from.
The trouble is that each text string does not follow a consistent order, so I
cannot use text functions such as LEFT, MID or RIGHT to extract what i need.
View 10 Replies
View Related
Oct 14, 2008
I was given a worksheet that contains a column (A) containing about 5,000 URLs (A1 to ~A5000). Each URL string includes three parameters that I want to capture the values.
For example =
[url]
The prameters are known but the values are random up to 256 characters.
I am trying to pull each of the values into a column for sortability and have been mildly successful using MID and FIND but no joy.
View 9 Replies
View Related
Jul 31, 2009
I'm trying to extract alphanumeric data before and after a numeric string. The numeric string is in the middle of a URL, which is a varying distance from the start and end.
Here's a sample of my data:
URL Page ID Headline Section quiz.impression-http://www.stuff.co.nz/2677193 2677193
http://www.stuff.co.nz/travel/267779...flying-JetStar 2677794 Why-I-hated-flying-JetStar travel http://www.stuff.co.nz/technology/di...eo-a-smash-hit 1675213 Wedding-dance-video-a-smash-hit technology/digital-living http://www.stuff.co.nz/national/crim...ce-in-Auckland 2678248 Man-shot-by-police-in-Auckland national/crime http://www.stuff.co.nz/sport/rugby/s...it-for-Blackie 3674350 NZRU-set-to-bend-it-for-Blackie sport/rugby/super-14
I start with just column A and generate the other three from that.
I'm using some excellent code from a Mr Excel guru (thanks again Peter) to extract the 8-digit numeric string in column B, I just need C (after) and D (before).
That macro is:
Dim Bits
Dim c As Range
Dim i As Long
For Each c In Range("A4", Range("A" & Rows.Count).End(xlUp))....................
View 9 Replies
View Related
Jun 21, 2007
I have a woorkbook where I have a cell that has the following apperance:
12345678-ABCDEFG
The numbers can vary in length and as well as the text. What I am trying to do is to split this cell into to columns. Column 1 shows the account number, Column 2 should show the Text and the "-" sign should not be in any of the columns ie it should look something like this
Column1 Column2
12345678 ABCDEFG
I have found a useful function for the first part thanx to Mikerickson
URL:Extract Numerical Data
Function midNumber(inputStr As String) As Double
Dim i As Long
For i = 1 To Len(inputStr)
midNumber = CDbl(Val(Mid(inputStr, i)))
If midNumber <> 0 Then Exit Function
Next i
End Function
But how do i seperate the text and how do Crete a macro that would loop through some 2000 rows and create a column A and B from Column A?
View 3 Replies
View Related
Dec 14, 2013
I have the data string below:
Career:25: 1-0-2 $13,765
I would like to extract the 1 between the : and - and as a seperate extraction would like te 2 between the - and the $ I have tried a few things but end up with the - as the length of the data changes
View 5 Replies
View Related
Apr 30, 2013
Is there a way to extract a phone number out of a string of text. The phone number will always be expressed the same way (eight digits seperated by a space half way through) - 9999 9999. But the text before and after the number is random. Is there a macro to do this on a large scale? The method needs to be applied to hundreds of these strings, each on a new line.
An example of the string of text with the phone number underlined:
Alesdro 0 63PnantCey4281 5919 Aledio daSisaon' Brglry
View 7 Replies
View Related
Jul 4, 2014
I have an excel stock price template, where I need the current price to calculate the mid point if the chart.
This is the text I get from the webquery
Underlying stock: SBIN 2699.00 as on Jul 04, 2014 15:30:36 IST
I need a formula to extract the stock price '2699.00' only from this text.
The problems are sometimes its may change to underlying "stock" to "index", SBIN to RELIANCE or TATASTEEL, and the price some times two digit say 16, some times 6 letters say 150.05, or 8 digits say 15160.00
Formula, the text in B1 and need the price at A100.
View 9 Replies
View Related
Jul 8, 2014
I am trying to transfer data from some appointment based software into a spreadsheet .
the data comes out as one row For example ,the following is the contents of cell A1: 06/06/2014 09:00 AM - 09:30 AM Patient: John Smith
What I would like to do is extract one column with the date ,one column with the patient forename and one with the patient surname.
I have tried various combinations of =RIGHT(A1,LEN(A1)-FIND(".",A1)) etc etc but cannot extract the data I need
View 3 Replies
View Related
Jan 13, 2009
Hey I got a long String like this "[...] increase of x.xx% [...]".
I am trying to extract only the percentage number which can be of variable length, so maybe 900.99% or 9.99%.
I tried this formula:
=MID(G14,SEARCH("%",G14)-5,5)
but this one doesnt bring the right results as the percentage figure is often not exactly 5 characters long.
View 9 Replies
View Related
Oct 2, 2011
I have a column of cells, each with a with text string that has three possible formats where I want to extract the total number of seconds.
The three formats are:
nnn hrs nn min nn sec (example: 1 hrs 42 min 58 sec)
or
nn min nn sec (example: 55 min 1 sec)
or
nn sec (example: 10 sec)
The result I want is a numeric value of the total number of seconds in the text string.
For example a text string of: 1 hrs 42 min 58 sec
Should give the result: (1 x 3600) + (42 x 60) + 58 = 6,178
View 9 Replies
View Related
May 8, 2014
I have about 2000 rows of text. Each row is a short write up about prospective new business. There is a reference to a projected dollar amount Within 'MOST' of the write-ups. In order to generate a report about the potential dollars being projected, I need to find an easy way to extract the dollar amount from the text.
In most cases, the projected dollar amounts are preceded by "FY2014" then a "$". However, there are a handful of cases where there is no FY2014, but some variation of the year indicator. Most of the dollar amount entries are written is short text - FY2014 - $5k, 2014 $15k. While some others are written out - FY2014 - $ - $20,000. In still other cases, within the writeup reference is made to the amount of product projected to be shipped by using the dollar symbols. For example - Estimated ship totals $$ for FY2014 = $5k. I've tried writing some formulas, but as in the last example - the first dollar signs are recognized rather than the dollar sign immediately before the value.
Sample data -
Estimated ship totals $$ for FY2014 = $5K New Customer Prospect 4/9/2012 Customer has still not decided if he
2014 $15K Funco 4/7/14 working on the program for the demo ...
Over 130 samples tested with about 90% accuracy. FY2014 Ship $$ = $20,000 at least. Setter Line has 7 plants ...
View 4 Replies
View Related
Mar 21, 2014
I have a string of information in an Excel download from PayPal that I need to upload into QB; but, the information needs to be in a different format. The string from the download is:
Property Address - 1116 South PaxonBorrower " Testa and Son Contractors,LLCLoan # - 1402088Acct Executive " Brandon Tully
But, I need it to be:
1402040;TPAPR;141 North Clinton Street, East Orange, NJ 07017;Leslie P Carter
Is there a formula that I can use to extract specific information from the original string into columns so I can then concatenate the columns into the information I need to upload? Or is there a better way to do this? The Property Address and Borrower Name are not static sizes so I can't use a formula that counts spaces.
View 3 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
Mar 1, 2007
How do I open a text file, read all the lines from said text file & extract a value in the form of a string. For ex., if I know that a certain line in the file might contain this text: Market Value=1234, then I would like to extract the value 1234 (the text Market Value is a constant) from that line & dumps it into a cell in the Excel file.
View 3 Replies
View Related
Jul 9, 2014
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?
View 3 Replies
View Related
Jun 29, 2014
I need to create a macro to do the following:
Search the activecell for a text string (a), and then either paste in text string (b) at the end of the cell if (a) is found, or text string (c) if (a) is not found.
For example, if the activecell has "AA/" in it, I want the cell to become "AA/01" (pasting in "01" at the end), and if the cell has just "AA" in it, I want it to still become "AA/01" (pasting "/01" at the end). The macro will be linked to a commandbutton.
View 7 Replies
View Related
Mar 28, 2009
refer to the attached workbook for reference. I am looking for a function in Sheet1, Column E that will search for the value of Sheet1, Column A within Sheet2, Column A. When a match is found, the function should look across Sheet2, Columns B - V for values of 1. When such values are found, the function should return the associated value from Sheet2, Row 2. There may be multiple values of 1, and as such, the function should separate values with a comma.
View 3 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
Apr 8, 2014
I have the cell data as below
How would I split into a new column the first part which is a date into a new column, then the country and the remainder into separate columns?
I still want the original data as I need to check that the splits worked well?
16.5.90 CH 1671/90-4
18.10.1991 CH 3056/91-1
24.07.92 ch 2341/92-2
30.7.92 ch 2395/92-3
18.11.92 Us 3533/92-5
26.5.93PCT 1577/93-0
9.8.93 CH 2363/93-8
17.8.93 CH 2445/93-0
25.1.94ch209/94-6;8.12.94ch3714/94-1
25.1.94 ch 209/94-6 ; 8.12.94 ch 3714/94-1
8.4.94 ch 1047/94-0
22.4.94 ch 1255/94-7
18.11.1992 CH 3533/92-5
18.11.1992CH 3533/92-5
View 2 Replies
View Related
May 12, 2012
I have a dataset of shifts and want to compare each shift that needs to be filled to a list of requests for time off (vacation, etc).
For one cell the code is: =IF(ISERROR(SEARCH(B1, A1), 0,1)
Where A1 is the column of requests in comma delimited forme (ie: "AB,CD,EF").
Where B1 is the column containing the person assigned to shift 1 (ie: "AB")
In this case, would return a "1" as no error was returned, as AB was found in the list. Here "1" would represent a schedule conflict.
Without creating many cells for each shift- there are 20 shifts- can I create an array or string together this type of "SEARCH" function?
View 1 Replies
View Related
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