I'm working on an email database and am looking to sort them into type. I am trying (without success) to use the IF function as follows.
If A1 contains the letters "isd" consecutively, then B1=TRUE
If A1 contains the letters "google", "yahoo", "hotmail", or "austin.rr" consecutively, then B2=true
Column C is the tricky one. It comes from the bank and somewhere in there is a 4-digit tenant reference. I did a formula to try and isloate it and it worked on most but if you look at the very first row, there is a spurious 99 in there, so it didn't work. Is there a way of EXTRACTING the first four consecutive numbers and placing them in another cell?
I'm putting together an Environment Threat Assessment for work and want to automate as much of it as I can to alleviate the amount of time ppl have to spend putting values in. My main problem is I have three very important columns: Likelihood, Success Rating and Risk Rating. Likelihood and Success Rating values will be selected from the following:
VL - Very Low
L - Low
M - Medium
H - High
VH - Very High
Each of these is given a value: VL = 1, L = 2, M = 3, H = 4, VH = 5.
The Risk Rating is the result of multiplying the Likelihood and Success Rating. So a Likelihood of Medium and a Success Rating of Low will give a Risk Rating of 6. Therefore, the cells on my spreadsheet would look like this:
Likelihood Success Rating Risk Rating
M L 6
The issue is I want a couple of things to occur. Firstly I want keep the values in my Likelihood and Success Rating cells as letters (ie. VL, H, VH etc) rather than numbers, while still generating a number result in the Risk Rating cells. Secondly I'd like to be able to change the colour of the Risk Rating cell to reflect the number it gets assigned. For example Green for < 8, Amber for 9 – 14 and Red for 15 – 25. I've been trying to do this with array formulas and am basically going around in circles with no success.
How to create space between two consecutive capital letters?
like i have....
RPerry
RCCox
i want...
R Perry
R C Cox
How to create space between two consecutive capital letters?
like i have....
RPerry
RCCox
i want...
R Perry
R C Cox
I have 1000s of rows of data wherein there is no space or full stop once the sentence ends and a new one starts. For example,
Original text: Bob is a boyJanice is a girl.
What I want: Bob is a boy. Janice is a girl.
In column A I have the following numbers
13710
14782H
9827
14782
14206B
a formula that looks at this range of cells and returns only the cells that have a letter at the end. The letter range goes A to M
I've got this problem: I need to separate around 40 alphanumerical entry in Column'A' to Columns'B','C','D','E'..
View 6 Replies View RelatedI have got to extract a series of mixed letters & numbers from a cell. The format of the data i need to extract is always [Letter][Letter][Number][Letter][Letter]][Letter][Number][Number]. The problem I've got is the notes field is not in a standard format as it can be anywhere in the cell.
The table below shows the notes cell & the Data I require.
Notes
Required Data
Fault number AB1ABC12 is complete
AB1ABC12
BC2ABC12 status is unknown
BC2ABC12
pending job ws1abc12
ws1abc12
I need to cut off addresses by 30 characters. I am doing this using the MID function. However, being a newb, how can I apply this function to say D4, D5 all the way to D899, without copy and pasting for 800 times? I did search this first, and couldn't find a resolution.
View 4 Replies View RelatedExcel.xlsx
Attached is the example of the data, I am trying to sum the amount applying 2 criteria, within a particular month and other category. The formula tried is
=SUMIFS(D5:D10,C5:C10,"*June",E5:E10,H1)
However it is giving a value of zero only. I have tried other combinations as well.
Supposing you would like to find the median of numbers in column D, but only for rows that have the word "Jones" in column A? Is there a way to do this? Obviously I know I could do a sort and simply specify the range myself, but we're dealing with nearly 2000 rows and a LOT of different values in column A
It occurs to me that it would be even better if I could ask excel, in effect, to identify every separate text string in column a and then find the median of the corresponding numbers in column D. In other words I wouldn't have to type any strings from column A at all.
I'm not sure what verbage to use in order to describe what I'm trying to accomplish.
Let me give you a piece of code and the question will be clear.
Function addValuesToComboBox(arg1)
arg1.AddItem("one")
arg1.AddItem("two")
arg1.AddItem("three")
End Function
I would like to use this function later in the code in the form of:
addValuesToComboBox("Combobox1")
addValuesToCombobox("Combobox2")
VBA will error out with "Run-time error '424': Object required". I know I need to declare the variable arg1 as a type in order to use it in this form, but I don't know the correct data type.
I also ran across a similar issue like this where the advice was to use the Set function in the form of:
Function addValuesToComboBox(arg1)
Set arg1 = arg1.AddItem("one")
Set arg1 = arg1.AddItem("two")
Set arg1 = arg1.AddItem("three")
End Function
How to use COUNTIFS Function only for the visible Data after applying filter.
Ex:=COUNTIFS(A:A,"Ret",P:P,"M")-COUNTIFS(A:A,"Ret",P:P,"M",B:B,"") it gives d result including hidden data, but i want it only for visible data after applying filter.
I am making an IF function that calculates and converts units, for example kg to g and g to kg etc.
This is the formula I typed in
=IF(E6=kg, ((F6/D6)*G6/1000), ((F6/D6)*G6))
When I evaluate it I get the #NAME error. What do I need to change to make it work?
I have a sheet with part numbers, costs and wish to add varying number to the cost in each row, based on a certain aspect of the part number:
A
B
C
[Code].....
In the example, 01/1X/2X/3X are the differentiating aspects of the part number. To rows in column A containing 01 (preceding the dash) I want to add nothing, 1X+.75,2X+1.5,3X+9.
I have researched the nested IF formula and can use it in a test as long as column A contains either numbers or letters. The problem I have is because there is both numbers and letters, the logical argument won't validate "if true". Here is my formula (omitting my attempts with parenthesis etc. around the letter in the logical argument):
=IF(IF(A1=1X,B1+.75,IF(A1=2X,B1+1.5,IF(A1=3X,B1+9,B1)))
I am trying to code a macro that will search through a selected range of cells for key letters, for instance this cell may contain any combination of B, C, Te, Tc, RH, or LH. I would preferably like to search with capitalization being a factor but it is not a deal breaker. Below is a sample of what i have if the cell has a B, C it works for B but ignores the C i need it t o recognize both.
Code:
If InStr(1, ActiveCell.Text, "B") Then Range("O" + CStr(ActiveCell.Row)).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
[Code] ........
I have cell A7 Merged and need info in that cell. Example of what I want: Say I click on cell A1 and it is the color Pink, but has the letters blue in it. I would like it to display the word "Pink" in cell A7 not the number. Well I would like when you click on any cell with colors, that it will display the color in Cell A7. When I try different ways all it gives me is the number in the last cell of the square not the color and when I click on other cells it will not give me that cells info.
Private Sub CommandButton1_Click()
Dim x, rng As Range, r As Range
Dim myList
Dim row1 As Integer
x = Application.InputBox("Enter size of square: 2=2 by 2, 3=3 by 3, or 4=4 by 4", Type:=1)
Set rng = Range("a1").Resize(x, x)
myList = [{1,2,3,4,5,6,7,8;6,11,3,10,13,16,38,53;"Brown","Pink","Grey","Purple","Green","Red","Blue","Yellow"}]
rng. CurrentRegion.Clear
Randomize
For Each r In rng
x = Int((8 * Rnd) + 1)
With Application.WorksheetFunction
r.Interior.ColorIndex = .HLookup(x, myList, 2, False)
r.Value = .HLookup(x, myList, 3, False)
Range("A7").Value = r.Interior.ColorIndex
End With
Next
With rng
.ColumnWidth = 10
.RowHeight = 50
With .Font
.Size = 14
.Color = vbWhite
.Bold = False
End With
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Borders.Weight = xlThick
End With
End Sub
In Excel we can apply different formatting styles to the "TEXT" contents of a cell while we are in edit mode. For example we can apply "BOLD" to Brown Fox and "UNDERLINE" to lazy in a string "A quick brown fox jump over a lazy dog.
But when the above string is concatenated from the contents of various cells, excel is unable to apply different formatting (as exampled above) in the concatenated cell. Although we can apply bold, underline etc by selecting the concatenated cell but it is applied to whole string and not to the selected parts of string.
Sheet1 *A1Brown2Fox3Lazy4*5A quick Brown Fox jump over a Lazy DogSpreadsheet FormulasCellFormulaA5="A quick "&A1&" "&A2&" jump over a "&A3&" Dog"
Moreover, MS Word's "mail merge" is capable to apply different formatting to different "Fields" in a single paragraph.
Is there any way so we can apply multiple formatting to the contents of a single cell which is not "Text" but result of "concatenate" in Excel, like MS Word's mail merge or as it is applied in custom format within straight brackets like [Red] etc.
I want to have a condition which will check if the value in cell is present or not and based on that i have to display result in a cell.
Eg: In Sheet1, in column D i have 4 values (Simple, Complex, Medium Complex, Very Complex)
In Sheet 4, we have numeric values to Simple, complex...Like below
Complexity Estimation
Simple 2hrs
Complex 4hrs
Medium Complex 2.5hrs
Very Complex 6hrs
In Sheet5, we have values defined in % like below:
Requirement Analysis
15%
Design
20%
Coding
20%
System Testing
15%
[code]....
In column G of Sheet1, we have to write a function or formula which we do below calculation.
IF (Sheet1!D3="Simple") then Sheet1!G3=Sheet5!B2(ie.15%) of Sheet4
Similarly i will have Sheet1!D3 values as Complex,Medium Complex,Very complex
is it possible to have more than one IF function applying to a cell eg. if value in cell A1 is greater than 10 then Positive is written but if value in cell A1 is between 8 and 10 then Suspect is written.
View 2 Replies View RelatedI have data from (row 1, column 1) to (row 53, column 5) on 283 consecutive worksheets in a singular excel file that I would like to be presented on a singular worksheet starting from the data on worksheet 1 and descending to the data on worksheet 283.
I am looking for a copy and paste loop solution that will copy the data from each page and sequentially paste the results on a singular output page in descending order (worksheet 1 data, worksheet 2 data... etc) so that I can sort the data.
I have quite a number of 4th degree polynomials and attempting to extract the variables from every equation. I used the formula below (from another thread);
=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$10000))))
but it only extracts the first variable.
4th degree polynomial function: f(x) = ax^4 + bx^3 + cx^2 + dx + e
Example of one of the equations: Y = -42.4276 + 319.989 X - 809.094 X^2 + 942.247 X ^3 - 397.587 X^4
I'll like to have the final result like below (Based on the Example equation above)
edcba-42.4276319.989-809.094942.247-397.587
Currently I am facing a problem that I want to copy data from sheet 2 to sheet 1.Here I am using vlookup function.But I have a problem that when there is two items of same name in sheet 1,it copies data from sheet 2 into both same items in sheet 1.How can I restrict and copy data to only 1 items.The sample data is as shown below :
Sheet 1 (Including duplicate item)
Apple
Apple
Ball
Cat
Cat
In sheet 2 (Unique item)
Apple 25
Ball 36
Cat 34
I have a Excel file with three tabs on it: 'Raw Data', 'Edited' & 'Upload Ready'. As you can probably guess, you dump raw data in the first tab, it pulls the second one where it is 'edited', which then goes through to the final one which contains pre-populated information required on each upload.
Now I'm not great with Excel, so when I get the formula to work as required in H2 (Edited tab) I just drag it down to HX as needed by the number of rows used in the Raw Data tab: View image: Excel drag
Is it possible to apply the formula used in H2 in H3:HX only if there is data entered in A1:AX in the 'Raw Data' tab? ... or rather I'm sure it is possible (I would guess using the 'IF' function') but I can't work out how to do it and then apply it to the entire column.
i have a list of 2000 fields which have the same format IE "AB10014"
I need to remove the "AB" from every field and leave the #.
Besides putting a space and running text to columns I'm not sure how.
I have words in cell range (i.e. A1:A1000) and I want them to became upper letters.
Excel forum to EXCEL FORUM
I have a column of cells, some blank, some containing just numbers, some containing just letters, some containing numbers preceded by the the letter 'p'
E.g.
frt
34.2
36
p34.5
In the cells containing the number preceded by the 'p' - i would like to remove the 'p' leaving just the number, with all other cells remaining unchanged.
I need to achieve the situation below with a formula. I have tried simply doing, for example, = B3+1 but this throws an error, probably because the value being checked is alphanumeric.
Data
A001
Data
A002
Add next number (A003) when data entered in cell to left
how can I seperate small and big letters from one collumn, example:
NAME
name
NAME
NAME
name
name