Question With Writing A Formula That Can Pick Out Words With 3 Letters
Jan 8, 2009
I have a question with picking out certain word in the column. I am trying to write a formula that would allow me to pick out names that has first 3 alphabetical letters from the columns. The column has a lot of different names, but the one that I would want my automation to pick out is similar to this, C, AR, AA-103, BG-2056, HJE-1100, etc. However, in the same column, I have other words like elevtar, hsvte, lvnrm, etc. that I do not want my formula to pick out.
View 9 Replies
ADVERTISEMENT
May 25, 2007
i can count how many "A" there is in a text put in 1 box and "B" to be put in another box etc.
View 9 Replies
View Related
May 10, 2009
I have a key-word, and I need to search a 90,000 words in a range where i should find several words that have 2, 3 or 4 letters of the first letter of the key-word, then find several words that have 2, 3 or 4 letters of the second letter of the key-word,
View 9 Replies
View Related
Apr 8, 2014
I have a reference written as a string in cell "AS4", the string is results!A4
I want to write that same reference in cell "as5". I have written the following
[Code] .....
However, cell "as5" returns the string as =results!'A4', with single quotes around A4
Therefore the reference isn't working.
View 2 Replies
View Related
Jul 20, 2006
Until now, I have been using the terrifically inefficient way of writing in a formula in a cell and copy pasting the value in my VBA
Easy example (concatenating):
Sheets(Sheet2).Select
Range("D1").Select
ActiveCell.FormulaR1C1 = _
"=""Data collected on ""&TEXT(NOW(),""dd/mm/yyyy"")&"" at ""&TEXT(NOW(),""hh:mm"")"
Range("D1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
More complex example (with IF statement):
Range("D2").Select
ActiveCell.FormulaR1C1 = _
"=IF(LEFT(RC[-3],13)=""No list found"",""Data requested for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& "" out of range"",""Data downloaded for ""&update!R[2]C[-1]&""/""&update!R[2]C&""/""&update!R[2]C[1]& """")"Range("D2").Select...............
View 4 Replies
View Related
Aug 17, 2007
I have a cell in which I use the following formula:
View 13 Replies
View Related
Mar 23, 2009
Is there a formula or function that allows me to always select the lowest (not min) actual lowest in a column? For example, I have a formula in A1 and I need it part of it to always reference the bottom of a certain range (i.e. A5.A200).
So one day the lowest would be A7 but a week later it could be A8. I don't want to have to keep changing the formula in A1 to find the lowest value. I will be adding data on a regular basis.
View 6 Replies
View Related
Dec 29, 2013
I'm working with a multi-page workbook I use for work scheduling purposes.
This is how Sheet1 is set-up:
A
B
1
Name
[Code]....
In the cells in Column D I have used the following formula =IF('Sheet1'!B2="Yes","Holiday",""). What I have realized is that this formula is quite limited. For example not all 12 colleagues work everyday and they work different shifts on different days- each day's data is sorted so that the colleagues are organized primarily by Shift start time and then alphabetically by name- consequently if I used this formula across the entire workbook the Sheet1B2 data may not be consistently referencing the same colleague.
I was wondering if there was a way to amend the formula so that if a colleague is marked as 'Yes' in Sheet1, 'Holiday' could be inserted in Column D in the rows corresponding to where the colleague's name appears?
View 3 Replies
View Related
Feb 13, 2014
I'm trying to create a "master file" that can be used later (a tool other uses can pull up) on with datasets of varying size. I'd like to "data dump" a table of individuals with subsequent columns of various characteristics, then automatically compute various things. So then a user can dump their data into my file and "get the answer" immediately. I've seen some stuff on running one calculation on a dynamic number of cells, but let me illustrate why this is different. I want to perform a dynamic number of calculations, see below:
Each row (record) is a person, with some characteristics (numerical). Ultimately I have a few columns on the end with some computations (Excel formulas) I'd like to do. I'd like to do this for each person (that is, each row) in that data that I dump in. But the thing is, ahead of time we don't know how many people will be dumped in, and that changes (because I'd like this to be a tool...). So how do I tell Excel to compute things if there is a person in that row, but do nothing and leave cells blank if there isn't a person? Ideally I don't have millions of ifs testing for blank cells either. I'd like to do this with Excel functions only and not VBA, if at all possible.
View 1 Replies
View Related
Apr 25, 2006
When I am writing a formula that includes a lookup, it returns the # NAME? error in the spreadsheet. If I delete the trailing ), retype it, and hit enter then the formula returns the correct value. How do I get the formula to work correctly as it is written to the sheet from VBA!
Here's the line of code that writes the formula to the worksheet:
TBox.Range("A3").Offset(i, 3).FormulaR1C1 = "= ROUNDUP((8-(VLOOKUP(RC[-3],K:L,2,FALSE)/1.2))/RC[-1]*1.2,0)"
View 4 Replies
View Related
Aug 9, 2007
I have numbers in K12 thru K23 (a few of the cells are blank)
The formula below is what I'm using to figure a golf handicap. I have 40 handicaps to figure this way.
=(((K15+K21+K19+K23+K18)/5)-37)*0.9
I manually picked out the 5 lowest numbers in column K for the formula. I think there should be a better/easier way. Can anyone help me?
View 13 Replies
View Related
Oct 14, 2008
See attached. This is a report that I have to do every month. I cant work out a formula for it. What I need is a formula that will pick out which is the largest Figure of column A,B,C,D for each row and put the corresponding header in column G. i have manually put these in. Would anyone know a formula i could use for this.
View 3 Replies
View Related
Feb 9, 2010
I have a formula that looks like this : ='G:STOCKS[1001 Stock.xls]Matl%'!$I$32
There are hundreds of similar formulas, all referencing different cells in the same file. The “1001” bit refers to the date, and next month it will need to say “1002” to pick up the new month's file. I would like to hold the “1002” bit in a cell (say A1), so that at month end, I just change that one cell, and all the formulas will point to the new file, but I’m not sure how to work that into the formula.
View 2 Replies
View Related
Jul 17, 2013
I have the following formula
=IF(VLOOKUP(F89,'2013 susp 2714035 Reissues'!F:F,1,FALSE)=F89,"yes",FALSE)
That is supposed to look at the tab 2714035 Reissues and find the same dollar amount. The issue Iam having is the dollar amount can be a negative or a positive amount but the formula is only picking up the number if it matches exactly negative to negative or positive to positive. formula to pick up the matching number if its a negative or a positive.
View 1 Replies
View Related
Jan 20, 2014
I am looking for a formula to remove the text 2.2 from the string Rav4 2.2 D-4d X or 1.33 out of Yaris 1.33 Vvt etc, I have tried using the formula
=IF(P2="","",VLOOKUP("*"&P2&"*",AM$1:AN$25,2,1))
But it just keeps returning #N/A. I have all the variables outlined in cells AM$1:AN$25 hence the reference. I currently have the formula
=IF(P2="","",VLOOKUP(P2&"*",AK$1:AL$25,2,1))
Which is working perfectly @ finding and returning the start of the string but just cannot get the one to find the middle characters.
View 6 Replies
View Related
Jul 10, 2014
I have a model that makes projections based on annual growth rates. However, I need to evaluate the data based on monthly intervals. With an 8% return on an investment of $1,000,000 my ending balance at the end of the year should be $1,080,000 and in year 2 it would be $1,166,400 and so on. In order to evaluate the monthly data I need each month in year 1 to be based off $1,000,000, so it would be 8%/12=.006666% or $6,666.66 per month. The next year would be based off $1,000,000 + (6,666.66*12) = $1,080,000 and each month would be $7,200.
writing a formula to evaluate over 360 periods.
View 5 Replies
View Related
Jan 23, 2014
I am a school teacher trying to adjust my tracking sheet to calculate pupils levels. I am looking for 2 potential formulas that will do the following.
1 - In cell AE I would like to generate a formula that will take the data entered in cells J:5, L:5, N:5, P:5, R:5, T:5, V:5, X:5, Z:5, AB:5 and AD:5 and give an average level.
2 - In cell AH is it possible to generate a formula that will calculate how many levels of progress the pupils are making - In other words I need Cell I to be calculated against cell J to see how much progress the pupils are making - for example if in cell I:5, a pupil is was given a 3a, and then in cell J:5 is given a 4b, they will have made 2 sub levels of progress. As well as this, can that progress then be averaged out across cells I:5, K:5, L:5, M:5, O:5, Q:5, S:5, U:5, W:5, Y:5, AA:5 and AC:5 to give an overall number of of levels of progress? An then..... can I colour co-ordinate the cell so that if the pupils are making 3 or more sub levels of progress it turns green, 2 sub levels orange and 1 sub level red?
Levels work like this
3c
3b
3a
4c
4b
4a
5c
5b
5a and so on
View 3 Replies
View Related
Oct 12, 2007
I am trying to validate a Grid Location, example: 22AX21321232
How do i validate letters in the formula?
View 10 Replies
View Related
Feb 15, 2014
I have two array formula's, one to count hours, the other to count days. Both are based on: type of worker, day of week, and week number. The problem is if I use a "V" or "T" for vacation or training, then the array formula will not work. Currently I leave the field blank if they are not working, but it would be nice to see whether they are on vacation or training. Is there a way i can have my cake and eat it too?
Counting Days
{=SUM(($E$5:$MN$50>0)*($B$5:$B$50=$D56)*($E$3:$MN$3=F$54)*($E$2:$MN$2=$E$67))}
Counting Hours
{=SUM(($E$5:$MN$50)*($B$5:$B$50=$D56)*($E$3:$MN$3=F$54)*($E$2:$MN$2=$E$67))}
Here is a breakdown of what each of the ranges mean:
$E$5:$MN$50 is the area which the hours are forecasted$B$5:$B$50 is the column for which type of worker (Electrician, Pipefitter, etc)$D56 is the specific type of worker we are counting for$E$3:$MN$3 is a row with all the week days ("Fri, Sat, Sun...)F$54 is the specific day we are counting for$E$2:$MN$2 is a row with all the week numbers$E$67 is the specific week number we are counting for
View 8 Replies
View Related
Oct 29, 2008
formula to add letters but with a numeric value. this is for a schdule sheet. where w would equal 7.5 and x would be 0.
i am using this
=SUMPRODUCT(--(ISTEXT(B3:H3)))*7.5
reads the w and adds up ok but need to be able to put w for work and x for off days and still add the total hours
View 5 Replies
View Related
Aug 18, 2008
Looking to create a Formula (not Code):
IF CELL A1
1. NOT Between 8 and 20 characters OR
2. NOT contain at least 2 numbers OR
3. NOT contain at least 2 letters OR
4. contain characters (e.g. punctuation) which are neither numbers or letters
Then FALSE.
View 9 Replies
View Related
Jul 29, 2014
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.
View 13 Replies
View Related
Dec 7, 2009
I have words in cell range (i.e. A1:A1000) and I want them to became upper letters.
Excel forum to EXCEL FORUM
View 5 Replies
View Related
Jul 24, 2012
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.
View 3 Replies
View Related
Apr 8, 2014
I have been trying to create a formula that will save me DAYS of messing around at work.
What I am trying to achieve is to have a sequence of numbers as follows:
BNA01A01 to BNA01A09 then have it change to BNA01B01 to BNA01B09.
This needs to be repeated for all letters to BNA01I09.
Then this sequence needs to be repeated to BNA12.
The last thing is for me to be able to change the formula in order to implement the same sequence on a separate sheet for BNB01A01 - BNB12I09 to BNL01A01 - BNL12I09
View 2 Replies
View Related
May 21, 2014
In Column A I have first names, In column B I have last Names, in column C I have id letters,
Column D another list of First Names And In Column E I have another list of Last Names
So what I need to do in F2 is Look at the name Last name in E2 (Lets say its Smith) then look down the Last names In Column B when you find a match look at the First name on the same row to see if the first 3 letters are the same as the first 3 letters in D2 if they are then put the id that's in cell C into F2 if not ""
I've been trying for hours but no luck, also if you do manage to do it can you tell me how you get it to look at the first 3 letters and how I could change that to 4?
View 9 Replies
View Related
Feb 28, 2007
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.
View 3 Replies
View Related
Jul 17, 2013
I am using the formula: LOOKUP(A1,{"S","M","L","XL"},{"S","M","L","XL"}) on a cell containing: EF-2147.(S) so that it will return a S [or M, L etc. if it were .(M),.(L)] but it just keeps returning #N/A. I tried putting in "(S)" etc. in the lookup formula but it then started returning the wrong letters.
View 2 Replies
View Related
Feb 26, 2011
I need creating a tool for numerology.
Every alphabet from a to z has a number associated with it.
A = 1, b 2 , c = 3 etc
Numeric Values for Each Numeral
A1N5
B2O7
C3P8
D4Q1
E5R2
F8S3
G3T4
H5U6
I1V6
J1W6
K2X5
L3Y1
M4Z7
I can either use excel or access or write this.
Example 1 , r = 2 , I = 1, L = 3. .
If I write RIL in column 1 , column 2 should say 213 and column 3 should add 2+1+3 and display 7.
Example 2:
Col 1 = Infosys Ltd
Col 2 should say 1587313 344
Col 3 = 1 + 5 + 8+ 7+ 3+1+3 3+4+4 = 39 , 3+ 9 = 12 , add 1+2 = 3
View 7 Replies
View Related
Jul 31, 2009
Can we unmerge a complete address in seperate seperate coloumns? Like Complete address is "1st Floor, 2a Harrison Road, Erdington, West Midlands, B24 9AA" and i want to split it like Address[1] "1st Floor" , Address[2]: "2a Harrison Road", Address[3]: "Erdington"...
Is there is any formula which can split data with the help of seperators (Comma or space) ??
View 3 Replies
View Related
