I have a problem when using the function of text(). I set "=TEXT(SUM(A1:A5), 0) + 5" in Cell A6. Assume the answer of SUM(A1:A5) is 10, the result in A6 will be 15.
My question is how to prohibit a string value [Text(SUM(A1:A5),0)] add a number value [5] to form the result 15.....
In the attached workbook i need to take out the % numbers from the text to populate the cells.
the Pay % cell is the % before the / and the receive % is after the /.
I am trying to put text form 3 cells into 1 string. I got the 1st 2 but the 3rd I only want to extra the first part of the cell up to the 1st comma. This is what i have so far:
[Code] ......
For example cell E81 would contain "abc, def, ghi" . I want to add to the string above only "abc" and not the entire cell content.
I'm currently working on a spreadsheet (see attached) which will have both numeric and text string data. This will be sorted by calender month and linked to another spreadsheet.
Could someone please list 2 functions for me.
The 1st would allow act like a filter allowing only data to be counted for a selected month from a drop down list.
The 2nd allows a counting of pre-set (drop down list) text strings to be counted.
Sorry seems a bit complicated and really I should have either a created a pivot report or used access but I'd like to continue with Excel for this exercise.
Is there a function in VBA that is similar to either the FIND function or SEARCH function in Excel? The arguments for the FIND function in Excel are FIND(find_text, within_text, [position]).
I have a text string in VBA ("$A1:$D$13") that I want to be able to identify the first "$" and then later the ":". I'm getting tripped up on the 3rd line of code. Thanks a million.
Sub page_set_print_area()
ActiveSheet.PageSetup.PrintArea = "$A$1:$D$13"
x = ActiveSheet.PageSetup.PrintArea
Position$ = Search("$", x, 0)
End Sub
This might sound like a weird question, but is there any way to prohibit a user to using the sort function in a particular sheet?
Filtering for values is okay but sorting must be prohibited so that no rows "change place" in a sense.
I have the following formula in cell H2:
=AND(EXACT(G2,UPPER(G2)),(LEFT(G2)>="A")*(LEFT(G2)<="Z")*(MID(G2,2,1)>="0")*(MID(G2,2,1)<="9")
*(MID(G2,3,1)>="A")*(MID(G2,3,1)<="Z")*(MID(G2,4,1)>="0")*(MID(G2,4,1)<="9")*(MID(G2,5,1)>="A")
*(MID(G2,5,1)<="Z")*(MID(G2,6,1)>="0")*(MID(G2,6,1)<="9")*(LEN(G2)=6))
I would like to prohibit data entry in G2 if H2 reads "false" and allow data entry in G2 if H2 reads "true" .
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 RelatedOn sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
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?
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 question regarding the right function. can you use a string within the function. i know that the function is written =right(A3,2). is it possible to write the function as =right(a3:z3,2). i keep geting an error with this function
View 4 Replies View RelatedI have a Sum function in a cell on a spreadsheet and a bit of code which checks it using an If statement. If the Sum equals 1 (100%) it should do one thing, if not do another. However, if I put in the If statement that the Sum cell value should equal 1, it doesn't work. If I put it should equal "1", it does work. Why?
Example that works:
If Range("LocalBirthTypes").Value <> "1" Then
MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool"
Goto Exiting
End If
Example that doesn't work:
If Range("LocalBirthTypes").Value <> 1 Then
MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool"
Goto Exiting
End If
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'm trying to create a formula to return the number of cells where the last 8 characters of a cell end with the string "landline"
My (failed) attempt at creating this is as follows:
=COUNTIF RIGHT(A2:A322,8)="Landline"
I am trying to make a function to take 3 Cells and using the initial Cell(say A1 with value of 100) as the inital value of a sum. Take Cell two(say A2 with value of 10) and add the value of A1 to Value of A2 in a constant increase. In otherwords A1+A2*A3= next increase. A3 is the value I want to change. But I want to add these values together as many times as the value of A3. so with current numbers will be 100+110+120+130+140 with A3 being a value of 5.
View 7 Replies View RelatedI would like to express an empty string with the expression .
[Code] .....
=if(D6='';0;1)"
[Code] ....
but I have an error message.
I don't know what is the error?
I try to merge two sting (one from function). the output of the following sub should be "ttgood". but instead, i got "good" only.
Code:
Function cs()
Dim sv As String
sv = "tt"
End Function
Sub ts()
Dim s As String
s = sv & "good"
MsgBox s
End Sub
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.