Condition Statement Based On 2nd To Last Character Of String?
Apr 10, 2012
I am trying to pull certain members loads from our global design force spreadsheet and because of the naming convention we used for our structural members the 2nd to last character is unique to what I am looking for. I am trying to get a simple condition statement that will display the load only if the 2nd to last character of the name of the element is satisfied.
For Example, if "T" is what I am looking for then :
EDT4 will be true and give me the value accordingly
D10T1 Will also be true and return me the value
D10B2 Will not be true
I know how to use the Mid() and right() function, but I need to check the 2nd from the right.
View 2 Replies
ADVERTISEMENT
Feb 22, 2009
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer.
I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form.
It looks something like this:
View 10 Replies
View Related
Sep 6, 2008
I've been working on this project to propagate certain products through multiple categories. I chose to use excel to assign multiple categories to each product. On sheet1 I have setup products (column a) and qualifiers in the following columns (color, model, etc). What I would like to achieve is for the user to select yes or no for each category column and and if yes then have the corresponding category breadcrumb string (from sheet2) inserted into one specific column in sheet3. As the user continues to select multiple "yes" from the category columns for that single product, those additional category breadcrumbs get inserted at the end of the list in sheet3.
View 9 Replies
View Related
Nov 17, 2008
I have a little problem in using wildcard character in IF condition.
eg:
--------------------------
For I = 2 to LastRow
If Activesheet.cells(I,5).Text = "ENF*" then
...........................
...........................
...........................
End If
Next I
--------------------------
I want the code to search for any word starting with ENF and then take the necessary action.
Could anyone tell me how to make use of wildcards in a situation like this.
View 15 Replies
View Related
Apr 26, 2007
The below is a formula I utilize to collect totals for individual associates.
=SUM(IF('01_05_07'!$C$6:$C$102="Petri, Pete #1",'01_05_07'!$AW$6:$AW$102,0))
What I would like to do is keep this same formula but somehow add a wildcard character so that I could capture all totals for Petri, Pete as there are Petri, Pete #1 thru Petri, Pete #15. Anyone aware how to revise the above formula. I've tried utilizing the * and ? characters but I must be putting them in the wrong place as I am not able to get it to work??
View 9 Replies
View Related
Mar 24, 2013
formula to find the last number in a cell and if that number meets a condition put a letter in the adjacent cell.
In cell A1 I have numbers as such 254671 (these numbers vary in length but not normally more than 8 in length). I want to know if the last numeral on the right is a 1 and if so then enter in the adjacent cell (B1) the letter y if not then leave blank.
View 6 Replies
View Related
Aug 4, 2007
I currently have a report that I have code on that will change the color of a cell based on whats in the cell.( Code posted below) However I also need to look at $J and if the first letter of the value in that column is a "z" then I would like to change that whole row to icolor 13 (purple).
For example J14 might be "Z-Thomas". if it does I would like that row 14 to change to purple.
Also I would prefer that the solution be in VB, so that we do not have to do something in VB with the fix below AND do something in Conditional Formatting.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer
If Not Intersect(Target, Range("$A:$E")) Is Nothing Then
Select Case Target
Case "y", "Y"
icolor = 4
Case "n", "N"
icolor = 3
Case "?"
icolor = 6
View 9 Replies
View Related
Jun 27, 2006
I'm looking for some VBA code that would look at a column of data, look at each cell value in that column and if the cell value starts with the letter 'G' and the 5th character in that cell is not 'Z' or 'X' I would like to highlight that cell. Example:G123-123 would be highlighted whereas G123Z-123 would not.
View 4 Replies
View Related
Jan 3, 2007
I would like to get the nth character from a string. For example, let's say there is a string "jupiter", and I would like to get the 3rd character of this string, "p". Is there a function in excel vba that would allow me to do this?
View 9 Replies
View Related
Feb 17, 2014
I have this data and want to extract the character after the letter Y if the string has a Y in it.
Example data Output
AU 2013 OD ANR B24 Y2 2
AU 2013 OD ANR B24 Y4 4
AU 2013 OD ANR B24 Y5 5
AU 2013 PD HLD NOV B SPA
AU 2013 PD HLD NOV C SPA
AU2013OD ANR B25 Y1 1
AU2013OD ANU B25 Y5 5
AU2013OD WCR FPVN B49
AU2013OD Y6 FPVN B49 6
AU2013OD WCR FPVN B40
AU2013OD WCR FPVN B43
View 6 Replies
View Related
May 30, 2014
I want to assign a value to a character or string
Like I have formula p2p3= (n-1)*p +(2*e)
I want to assign values(number) to n,p,e.............so that i can get p2p3....like want to assign 'n'=4
I don't want to use cell number for calling values of n,p,e
View 1 Replies
View Related
Feb 11, 2010
How can I evaluate just the first part of these stirings so I can just do something like Left("Ca",2)?
View 2 Replies
View Related
Jul 28, 2008
How do I split the below character or similar strings into 2 columns of 25 characters each without cutting a word off?
BONE CURETTE-RVERSE ANGLE 5.5MM WIDTH-MEDIUM/BAYONETED
View 14 Replies
View Related
Feb 9, 2012
I have a web query that returns a city and state, but the last character of the state is lower case. I need VBA code to change the last char to upper case. Here is what I have so far:
Code:
tmp = Cells(4, 1).Value
Mid(tmp, Len(tmp), 1) = UCase(Right(tmp, 1))
Cells(4, 1).Value = tmp
Is there any easier way to do this without 3 lines of code?
View 2 Replies
View Related
Dec 21, 2012
I have some columns in the excel file:
/path1/xyxxx/cccccc/filename12.txt
/path1/bxgdgg/gfdfacc/filenameeee8.txt
/path1/tttwrw/ccefecc/ddddd/filename56.txt
And I would like to delete everything before the LAST slash(just filenames)
filename12.txt
filenameeee8.txt
filename56.txt
View 2 Replies
View Related
Oct 11, 2009
I have a column that has various codes such as "E123" and "X456" and "S345". I just want to extract the numbers and leave the letters in each cell. How do you write a formula using the LEFT function to extract the last three numbers?
View 9 Replies
View Related
Jun 11, 2014
I have a cell that has approx 22000 characters. I'm trying to remove a specific character string from a cell by doing a find and replace with "". It works for characters in the first part of the cell but not for characters in the last part of the cell.
Example: I do a find/replace for the characters 21242 to "" in column A and I get the expected results. I do a find/replace on 69294 to "", again I get the expected results, but if I do a find/replace on 85203 to "", I get "Excel cannot find what I am searching for" (but its there!)
My cell size is within the max size of 32,767 characters so not sure why its not working.
Attached a sample worksheet.
View 5 Replies
View Related
May 23, 2007
I would like to find the position of the last occurrence of a character in a string. For example, I have a string with the following:
"c:wwgpeToolbidsTest File.GP$".
I would like to find out the position of the last '' in the string because I want to pull the filename, 'Test File.GP$' into a variable. I would like to avoid writing code to do this. Are there some Excel functions I can use for this?
View 7 Replies
View Related
Jan 22, 2009
I have a macro that asks the user to enter a file name and have written a small function to search the name given to the file to find any invalid characters, trouble is I must be doing something wrong as I doesn't work. Everytime I write something in the box I'm told it's invalid, regardless of weather it actually is or not.
Function -
View 7 Replies
View Related
Jun 12, 2014
I am trying to work out how to get nth position of a character in a string. For example in this
Code:
kjishdfiuayigdscka
if I use the formula
Code:
=FIND("i",I$1),
it will give me answer 3. But I want the position of 3rd "i" in the string. The answer should be 12. How can I change this formula to get the correct result?
View 9 Replies
View Related
Jan 29, 2009
In cell A1 contains a string, how do I get the number of position of the last numeric character from right to left (this value will be in cell B1).
Example: 12543AR3372C31WWW (In Cell A1)
4 (In Cell B1)
View 9 Replies
View Related
Sep 23, 2009
The barcode lablels all have the following format: A318639....that is a letter followed by six numbers.
When I scan the barcodes into any cell, a dollar sign "$" appears in the front of the barcode string that is entered into the cell. The dollar sign is not a relevant part of the string of characters in the barcode label.
What I am looking for is a solution where upon scanning a barcode into any cell, the dollar sign is removed from the front of my string and I am left with just the seven character string which is shown above.
I have experimented with the Data Validation tool by trying to limit the number of characters that are allowed in a cell to seven characters, but that has not actually removed any characters from my data string.
View 9 Replies
View Related
Jun 10, 2009
For icount = 1 To LenComputername
valComputername = Asc(Mid(UserComputername, icount, 1))
Next icount
If the computer name is NAMTOK-PC Then the LenComputername is 9. Does that mean then that the valComputername is equal to 78?
View 2 Replies
View Related
Jun 29, 2008
on the web I've found this formula, which returns the postion of the last occurrence of the character in cell B1 in the string in cell A1:
=MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=B1)*ROW(INDIRECT("A1:A"&LEN(A1))))
How do I turn this into a VBA function?
View 4 Replies
View Related
Oct 28, 2009
Rows:
Each row contains a new client (a new entry)
Columns:Clients first and last name.
Case number
Case type - Case type can be AAA, BBB, CCC, DDD, EEE, (and many more).
Case outcome - Case outcome can be Granted (G) or Denied (D)
A case can be of type AAA, and be Granted.
A case can be of type MMM, and be Denied.
A case can be of type ZZZ, and be Denied.
Etc...
I am trying to keep track of how many cases of each type have been granted and denied. If you look at the file i attached, you will see that the table is a list of clients, with different case types and different outcomes to their cases.
The problem i am having is that i need a way to have a two-condition statement as such:
if case type is "AAA" and case outcome is "G", then add 1 to a "AAA GRANTED" column on another sheet.,....................
View 3 Replies
View Related
Jun 27, 2006
I have a problem with a SUMIF statement. I think it is =SUMIF(AND(...
I have attached I want to sum in column E lines 255-316 if in cell D 34 and if cell B25 equals GL and cell C255 equals 0001.
Maybe this would be handled better with a sumproduct formula...
View 3 Replies
View Related
Nov 20, 2006
I'm having a problem with negative values. Somehow if the pitchX is -11.1877, let's say, and it is the right value, the code will still perform the 2nd if statement, when it should not. Am I using the wrong data type? Or is there anything I'm missing out?
Dim pitchX As Double
If pitchX > 10000 Then
pitchX = pitchX / 1000
End If
If pitchX <> Range("C8").Value Then
With Range("C8")
.Font.Bold = True
End With
End If
View 6 Replies
View Related
May 26, 2009
i want to extract a value from a column into another column in excel.
this first column contains text and i want to extract a value that in located between the characters 'FOR' and 'MTHS'.
how do i do that?
the situation is the same for all the rows; the value that i want is just located between these characters.
is there a simpler way other than using the MID functn? because everytime i use it i need to count the no. of characters and its quite difficult to keep up with the numbers.
View 2 Replies
View Related
Jun 16, 2012
Title should read: Finding the nth Occurrence of a character within a text string
I have a very long text string that is delimited by about 50 "/" to segment certain values within the text string. I want to be able to extract the text between the 33rd and 34th occurrence of "/". How to do this?
View 5 Replies
View Related
Feb 18, 2013
I have a code which displays the values of R2, S2, T2, and U2 in one cell (J2). R2, S2 and U2 are all letters, but T2 is a 2 digit number. If there is a leading zero in T2, then in the display of J2, the leading zero is gone. so if the value of R2 is K, S2 is M, T2 is 01 and U2 is S, then I would want it to display KM01S, but it is only displaying KM1S.
View 1 Replies
View Related
