i tried method like mid() with concatenate() to add in "0" after "/" to the below:
to make the format as in this standard: PD13/05-06/10/02
PD13/05-06/06/02
PD13/09-10/10/02
PW08/30-31/01/01
PA13/35-36/14/02
Find a short text string in a column of longer text strings and when that short text string is found return the longer text string that matches.
View 3 Replies View RelatedI 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?
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.
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 RelatedHow 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.
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 RelatedI 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
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?
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.
I have a column of data with letters in each cell, no numerical, only alpha. Now, some of those cells contain the letters "adj sub" as part of the text string in each cell. "Adj sub" is always at the beginning of the text string. As an example, a cell will look like this - "adj sub mhm". I want to delete rows whose cell description does not contain "adj sub" as part of the text in the cell.
View 5 Replies View RelatedI am working on modifying this code (below) that I found here: VB Macro to search webpage for text string
Code:
Option Explicit
Public stResultstr As String
Sub SearchForString()
Dim rngURL As Range
Dim cll As Range
Dim stCheck As String
Dim xmlHttp As Object
[Code]...
So right now I have the script prompt you for a URL range, then it asks you for what you want to search for, and I have it set to return the next 10 characters from that found point.
You can see here:
Code:
If InStr(1, stResult, stCheck, vbBinaryCompare) > intt Then
intt = InStr(1, stResult, stCheck, vbBinaryCompare) + Len(stCheck)
I began to create something to identify the last position of the found item because next I want to loop it to find the next occurance of the same thing and return the next 10 characters after it in the next cell to the right in that same row:
Code:
cll.Offset(, 1).Value = stResultstr
Again, what I want to do here is find and parse out every occurence of a string (inputbox) found on a web page url in column A. The parse occurrences will go to the right in cells C though ? for row N.
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 RelatedCell H1 has a variable string of references for eg
"FI570783AQ3516346EQ3516346FXVB123456"
I want to return the reference beginiing with "FXVA" so it would ignore the rest and only return FXVB123456 - this should always be 10 characters.
Just to add another complication to the mix, there may be 2 "FXVA" references in the string and i want to get both (these can be in the same string so FXVB123456FXEL123456 - but this might not happen regularly.
I have a sheet in which some of the cells have two strings separated by a linefeed. I have come up with a cumbersome formula which will let me check if either of the two strings is a member of a list stored on another sheet. However, it fails if there is only one string in the cell, presumably as there is no linefeed for the formula to find. How can I modify the formula to cope with this situation?
There are also on occasions, three strings in the cell, but I can't seem to access the middle string with the formula. Simplified spreadsheet attached to show the problem. This must be formula-based, as we have a no VBA policy. If you think there is better way of doing this, please let me know.
I have a report that in column BX has a large string of text (html information). Within this text there is a phrase called "| Hear = Education Website |" The phrase Education Website can be a variety of things though (Up to 9 choices). What is a formula I could use to just pull out the text after "Hear =" but before |""?
View 8 Replies View RelatedI have one column full of Item #s and Descriptions. I have another column that I want JUST the Item# in it.
For example:
Column 1:
Gyroscope REF#1234 Bike Thing
Column 2 Should Be:
1234
Column 1:
BallWall Bikes Ret# 12456 Helmet Thing
Column 2S hould Be:
12456
I don't know how to do this (I do know how to do VBA mildly, and I am pretty good with formulas). I also have a database of every Item# that could be in that cell.
I have a bunch of these following below which I need extract text from the first "." from the right hand side.
The amount of text and "." can vary as does the number of characters.
XXXXXXXX.XXXXXX.XXXX.XXX.XXXXX.XXXXXXXXXXXXX.XXXXXXXXXX.Vibrating Screen SC409
XXXXXXXX.XXXXXX.XXXXX.XXXXX.XXXXXXXX.XXXXXXXXXX.Scalping Circuit 4
XXXXXXXX.XXXXXX.XXXXXXX.XXX.XXXX.XXXXX.Fines Stacker ST601
Ultimately i want it to display the following:
Vibrating Screen SC409
Scalping Circuit 4
Fines Stacker ST601
I have used LEFT, RIGHT, SEARCH, LEN functions in various combinations but cant get it to reference the last "." from the right.
I am trying to remove a middle initial from a text string however not all of the cells have middle initials. In column B I would like to return the first and last name. If needed I can have the first name in column B and the last in column C then combine them.
Example
John A Smith John Smith
John Smith John Smith
I am trying to extract numbers after a specific text in a text string, for eg :abc SN 12345 xyzedf SN No. 456 mnoAs 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".
View 5 Replies View RelatedI 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.
As per title, I am trying to compare a column of text cells which contain "Yes" or are empty and a columns of numbers. If they are "Yes" and "1" on the same row, I want to output an "OK" message. Excel seems happy with the following code but it does not work and returns an empty cell if the two conditions are true.
[Code] .....
Here's an interesting one:
A1 contains a text string which is both bold and unbold (
B1 is blank
I need a macro which scans the cells with text and copies the BOLD portions of the text string into the adjacent cell.
Example:
A1
The sky is blue
Macro is run
B1
sky blue
I would like to take a string such as R0-H6-D2 and return a number (1-4) based on one of four values for R, H and D in a separate column for each. The attachment should make what I am trying to do clearer.
Substitute.png
I have trying to write a sumproduct formula, however, the total sum amount that i get is "0", which i think the formula is not picking up the text.
=SUMPRODUCT(($Y$2:$Y$65536="Evaporative cooler*")*($Q$2:$Q$65536=$AM$5)*($N$2:$N$65536=6),($AA$2:$AA$65536))
Y column is the text column to find the Evaporative cooler, the Q is another column where i have write the text out at AM5, N is a number field. The only bracket that is not working is the Y. Can you please let me know if I did anything wrong to it?
There are 3 types of Evaporative cooler, 1) Evaporative cooler - phoenix, 2) Evaporative cooler - Cal, 3) Evaporative cooler - Brisa. I wondered if I can use "*" to select all of the Evaporative cooler, instead of listing all three.
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
Can you please assist in helping with a formula that will return the value of the characters (no matter the length) between the "."?
123456.B.78.C.321
12345.B.78.C.321
I need to state whether a particular word, string or text in one cell, appears anywhere in a sheet.
Attached example: does the word AAAAAA in cell A10, appear anywhere in the range A3:A6
I need to be able to drag this formula so need a generic formula rather than adding specific text in speech marks to it.
Below is the code I started:
[Code] ......
The range contains a list of names. I want the sub to start with the first name and sum the value(long) which are in 7 columns to the left of the text string. Once this procedure has been done for the first name, it should go back up to the second name and do the same and so on. Once done I will return the name (cell A1 in another sheet) and its respective sum (cell B1 in another sheet) so that I have the list of names with their respective sum.
Sum function using an offset of 7 columns to the left?
I need to be able extract a string from within a longer string. The information I want will always start 5 chracters in and be bordered by an underscore either side, but could be of any length. For example in the following string, I would want to extract WF602.
DDR_WF602_____02_00_001_FLANGE_WR34_4_HOLE_PLAIN_CLEARANCE
I've been looking at the RegExp functions but can envisage problems with this as later parts of the string (WR34) have the same form as the part I need.
I can get rid of the first 4 characters using
The following line finds last "non-blank" row in a column. I have a column that has formulas in 10000 rows. Most of the time only first 7000 rows will have data, other cells below will have "". I need to find the last row with real data, not "".
Is there a way to add a condition or check to the following line?