Return Value Based On Last Number In Alphanumeric Text
Jan 25, 2008
I want to be able to return a text string of either Trainee 1 or Trainee 2, (or default to other text) based on codes that are 4 digits (DD01, KKB1, KKB2, KKC2, GG03, etc, where starting with K and ending with 1 or 2 is the criteria for TR 1 or TR 2 . There are two many KK** items to list them all in an If statement and I can't seem to use a wildcard.
=IF (right(c1,1) = "1", "Trainee 1", B1)
gets me halfway there I think but of course doesn`t pick up Trainee 2 and also returns Trainee 1 when it sees DD01.
View 7 Replies
ADVERTISEMENT
Feb 15, 2008
I have a range of cells B2:20 which has text in it. The range is sorted by cells A2:20, and I would like to do this without doing another sort if possible. The data looks like this in column B:
CTR848
AWT101
LMT177
AZG884
BRE112
BCW333
CTQ553
In one single cell (O1), I'd like to have the information:
AWT101
Is this possible to do without using VBA?
View 7 Replies
View Related
Aug 27, 2007
I have a range of some 2,000 plus cells that are various numbers in them only. What I want to be able to apply is a macro that will look at the number in a cell and if the result is between a set range, produce a text, eg.
If the number is between 0 - 999 then insert text [url] this is where you go'.
If the number is between 1000 - 1999 then insert text [url] this is where you go 2'
If the number is between 2000 - 2999 then insert text [url] this is where you go 2'
etc.
I have up to 11 ranges of numbers to insert (up to 10000 - 19999).
So the text is the result of the formula.
View 9 Replies
View Related
Oct 2, 2012
I have data on two spread sheets which I am trying to cross reference. On the 1st spread sheet I have a list of product codes e.g. 22886, On the 2nd Spread sheet I have a list of product titles which all include product codes within the text e.g. CHRISTMAS FANCY DRESS COSTUME - SANTA SPARKLE OUTFIT 22886 M
I need to insert a formula into the 1st spread sheet which will tell me if based on the product code it is present in the 2nd spread sheet. I have tried VLOOKUP and I think because I am trying to search for a code within a text string it keeps failing! All cells are general cells but the position of the code within the titles varies.
View 7 Replies
View Related
Oct 15, 2007
We use excel to organize salaries and contracts for my fantasy baseball league. I need an easy way to go through all the teams and subtract one year from each players contract. This is an example of a team.
The contracts are in the columns like this NG-3
I would like to change that to NG-2
View 9 Replies
View Related
Apr 4, 2008
Is it possible to increment / decrement cells using wild characters? I've found myself needing to increment values at different position, for e.g. :
ABC-###-FR05-AB
ABC-222-FR##-AB
C##445-KJ
where I would want to be incrementing # placeholders. It would help if I can give it a start number, and the increment amount (which can be different than 1).
What about possibly extending it a bit more to include two or more place holders at the same time as follows:
ABC##- FR*** (where # would have its own starting number and increment value, and * would have its own).
View 9 Replies
View Related
Feb 27, 2014
I have two columns in a spreadsheet that I want to use an IF function with. In the first column, I want a pick list containing 5 text options. Depending on which option is chosen, I want a number to auto-populate in the second column.
The numbers are important, because I want to repeat this pair of cells with slightly different text values in the second pair, and then to have a column that multiplies the two resulting numbers and conditional formats the result.
View 14 Replies
View Related
Aug 14, 2007
i wanna convert the decimal number to a round number...like 2.0L to 2L.
View 6 Replies
View Related
Apr 10, 2008
I am having an issue with formatting a cell to 'general'. I have a macro that runs using a formula (i.e. =MID(A12, 18,5)) to extract certain characters from an external data source. I then use only the values of that formula. There then is another macro that compares that value to another value in another worksheet. The issue I am having is when the macro runs, the code is read as ActiveCell.Offset(0, 3).Value...but instead of returning the value in 'general' format, it returns it with what appears to be text (i.e. "75.50" instead of 75.5). This acts as a mismatch since the quotes appear.
I have attempted several things to correct, but even if I try to format-->general it maintains the quotes. The only way it changes is if I click within the cell...then it turns to general (i.e."75.00" becomes 75.5). I am not sure what is the issue.
View 2 Replies
View Related
Feb 21, 2014
Formula to match all text and closest number in alphanumeric string
View 1 Replies
View Related
Aug 14, 2014
Wondering if it is possible to generate a random 4 digit number based off an alphanumeric string?
Example;
Cell A1 has 123XVF1234
Cell A2 has 321AFW4321
In B1 I would like to have a 4-6 digit number that is generated based on the alphanumeric data in Cell A1 (and so on down the list). If that is possible, I would also need to be able to convert back the 4-6 digit number back to its original alphanumeric value
Example;
If B1 returns 643562 it would need to be able to be converted back to 123XVF1234
View 7 Replies
View Related
Jul 14, 2009
I'm looking to return in cell BG2, the highest alphanumeric value from 10 non consecutive cells (highlighted yellow). When I use the MAX function it works fine should I only have numbers in the 10 cells, but the values in the cells very from 1A to 5Z [all instances of these values are shown in a table on sheet2 with their 'scoring']
View 2 Replies
View Related
May 2, 2010
I have a simple list of alphanumeric asset numbers,
AKR DC 0001
AKR DC 0002
AKR DC 0004
And so on, I want to run a piece of code from a user form which will return the highest value in the range plus one on the numeric portion. I can find the largest value easily with a sort and last used cell value and I could drag out the numeric portion, increment and crowbar it back in but I'm sure there is a more elegant way.
All asset codes follow the same naming convention and all reside in column a of the active worksheet.
View 9 Replies
View Related
Dec 4, 2008
Anyone know how to do this?
View 9 Replies
View Related
Aug 8, 2008
I have a column with a list of SKUs (item identifiers) that are actually combination of letters and numbers. They start with one of the following 4 letters: A, B, C, D
I would like to return the highest sku for each of the 4 letters. For example, Excel should look up column A and return "A3500" in Range(D4) as seen in the attached image.
View 3 Replies
View Related
Oct 15, 2007
I am trying to Lookup a country to see if it is classified as a "Developed Country".
My formula would be in cell AA4. I want to see if the country in cell B4 is on a list of developed country's on another worksheet. IF the country is found on that list cell AA4 displays Developed. If the country is not on the list, cell AA4 displays Emerging.
I have tried an IF statement using the Match function and it does not work.
=IF(MATCH(B4,'Developed Country List'!$A$2:$A$37,0), "Developed", "Emerging")
View 4 Replies
View Related
Jun 7, 2014
Here is my formula but I want to change the "wd" to just text (no matter what text is inputted"=IF(C5="wd",MAX($C$4:$C$23),C5)+Q5
View 2 Replies
View Related
Feb 10, 2009
Formula: ......
I am looking for the value in column G, a formula that will search B-F and return the Group with the highest percentage for each zip.
View 3 Replies
View Related
Sep 8, 2007
Spreedsheet on on sales and taxes
Need formula for cells.
3 different cells involved... B1 will have $ amounts, B10 tax code, B25 will have total of tax applied due to code.
Exp; cell B10 will contain the tax code... if you enter..... "1" in the cell the calculation will be 7% in cell B25, enter "2" in cell B10 and the calculation will be 6% in cell B25 , and if you enter "3" in cell B10 the calculation will be 13% in cell B25.
View 6 Replies
View Related
Sep 3, 2009
I have textbox1, which is updated with a date and time value formated as DD:MM:YY HH:MM and textbox2 value hich is also updated as DD:MM:YY HH:MM. I want textbox3 to deduct 2 from 1 but it won't do it I have tried:
View 5 Replies
View Related
Dec 15, 2006
I am using a MID fn to extract a single digit from a numeric string. I then use an IF Function to return a value based on the extracted digit. My IF statement works fine on manually entered digits but doesn't recognize my MID fn result. What am I missing?
View 6 Replies
View Related
Aug 3, 2012
Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:
id
event_name
C83-858
sample_1_arm_1
[Code].....
View 3 Replies
View Related
Dec 8, 2011
I have a range of cells (D10:D20) with numbers in them. I need cell A10 to look at D10 and return a specific number based on a set of criteria.
The same thing would go for A11:A20 & D11:D20.
If the number in D10 is 1-2 then A10 needs to display 0
If the number in D10 is 3-8 then A10 needs to display 0.1
If the number in D10 is 9-14 then A10 needs to display 0.2
If the number in D10 is 15-20 then A10 needs to display 0.3
Once I have that formula I know that i will just highlight A10 and drag the formula down through A20.
View 9 Replies
View Related
Nov 17, 2006
I have a questioner in a excel spreadsheet. Column C have yes or no answers. If answer yes is implied then I want the number 2 to appear in Column F of no is implied then the number 3 will appear. I am having trouble writing the language for this.
View 2 Replies
View Related
Feb 20, 2008
I have the following formula: =IF(C319<=300,9,IF(AND(C319>300,C319<500,6.75),IF(C319>500,6))) It returns 9 if C319<=300 but returns FALSE for all other numbers. I want the funtion to return 9 If <=300 and 6.75 if C319 is greater than 300 but <=500 and if it greater than 500 it should return 6.
View 5 Replies
View Related
Apr 29, 2014
I am looking for a formula to return the column number of the array when a specific text is found, in this case the text is "Yes"
The Array will only ever be 5 cells beside each other in a row
for example, M4:Q4, will be as follows - No No No Yes No
I want a formula to look at these 5 cells and return the number 4 as that is where the "Yes" value is
View 5 Replies
View Related
Aug 27, 2013
I have a set of data (Data tab) that lists name, a high number, a low number, and the state that is associated with the name and range between the high and low number (see attachment). I am trying to figure out a formula that will return the state value based on a match of the name and the number between the high and low. I am trying to use Index/Match but i cant figure out the "between the high and low" portion.
tester.xlsx
View 2 Replies
View Related
Aug 27, 2007
i wish to number the cells bases on the cell on the right.
Heading
A
A
A
A
A
A Total
B
B
B
B
B Total
C
C
C Total
D
D
D
D Total
to
Heading
1A
1A
1A
1A
1A
1A Total
2B
2B
2B
2B
2B Total
3C
3C
3C Total
4D
4D
4D
4D Total
The values in place of A,B...can range between 1 to 6.
View 9 Replies
View Related
Mar 28, 2014
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
See attachment : Example for forum.xlsx
View 3 Replies
View Related
Jun 2, 2009
Example: I have 2 sheets, a pivot and a data sheet. When selecting a different option on the pivot i want information returned from the data sheet (which is explanations of the information contained in the pivot) I need to add 2 criteria points.
View 5 Replies
View Related
