Finding Nth Occurrence Of Zero And Non-Zero Character
Aug 24, 2012
Let's say I have numeric nonzero and zero data in A2:X2. How do I find the first, second, third, second-to-last, last occurrence of a nonzero or zero value. I am able to find the first nonzero value by using Match(true, A2:X20, 0) to find the relative position, and then using index to find the value. This method also works with the first zero value, but I can't seem to find how to do the second, third, nth occurrence.
View 3 Replies
ADVERTISEMENT
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
May 28, 2013
7900 Personnel:7980 PR taxes:7985 Medicare
I can use the FIND function to find the first occurrence of ":" and therefore select everything to the right of it, but how do I locate the second occurrence of ":"?
I want to segregate the 7985 Medicare. What if the data varies, i.e. some rows have one occurrence of ":" and others have two?
7900 Personnel: 7970 Bonus. I always want to segregate the last 4-digit account number and description.
View 6 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
Jul 30, 2013
I need to remove the first character in each row of a column, but only its first occurrence in each row. For instance:
Say, column 2 has following data (lots of rows, by the way):
First Text" - "blabla"
=SomeText2 = "blabla"
SomeText3 = "blabla"
=SomeText4 = "blabla"
-SomeText5 --- "blabla"
........
....
I would like to use the VBA code to remove ONLY the FIRST occurrence of the FIRST character specified (either "=" or "-") in each row in that column, so that I get:
First Text" - "blabla"
SomeText2 = "blabla"
SomeText3 = "blabla"
SomeText4 = "blabla"
SomeText5 --- "blabla"
......
...and so on...
I tried to use this:
Code:
Columns(2).Cells.Replace What:="-", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Columns(2).Cells.Replace What:="=", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
but it replaces ALL occurrences of "=" and "-" and that is not what I need.
I also tried this code:
Code:
With Range("B:B")
.Value = Replace(.Value, "=", "", 1, 1)
End With
But It doesn't work either.
View 7 Replies
View Related
Mar 30, 2011
I have a column of text where I need to remove all the characters to the right of the last occurance of a special character.
I think a process like reading from right to left, look for the first occurance of the special character, and return the characters to the left of this position.
If I can determine the position of the last occurance of the special character, I could use the LEFT function.
The SEARCH function is close. It finds the position of the first occurance of text inside text but it reads from left to right. I need to read from right to left.
Another approach is to examine each character one by one from right to left. If the character is not the special character, delete it. When the character is the special character, delete it and stop the process.
There is no consistency in the text. The total lengths vary. The number of times the special character occurs in the text vary. The number of characters to the right or left of the last special character vary.
I much prefer not to have the solution be some VBA because I need to share it with others who are even less capable than I am. We are using Excel 2003.
View 5 Replies
View Related
Oct 23, 2002
What is the easiest way to find the last occurrence of a value in a table using functions. I want to avoid VB if at all possible and note the row number and use it in an index function to report text adjacent to that last occurrence. I would normally use match but match only records the first match and not the last.
View 6 Replies
View Related
Jun 15, 2009
I’m trying to figure out how to show the highest occurrence of text in 10 cells which are not next to each other. Some of the cells will also be blank And the other thing is if there is a draw e.g. 5 2Z and 5 1Z I'd like it to show that, either with a word e.g. "DRAW" or anything really other than just putting whichever of the 2 "notes" in it feels like
Attached is a spreadsheet (all other data deleted) that shows what I am wanting to do – I have typed the results I am after directly in to the cells in columns BE & BF – BE will need some kind of countif, but when I try that is says I have too many arguments! I have to leave the columns in between blank in this case as they have other info in them, which is really annoying as if those 10 cells were contiguous then the formula
View 3 Replies
View Related
Jun 25, 2008
I've read the how to for finding the nth occurrence using index/match but the example given does not really help solve my issue. The file I've attached is a condensed version of the actual file, which has more columns but I deleted all but the necessary ones for clarity. What I am trying to accomplish:
On sheet1 there are three columns, Business, Amount, and Closing Date.
Not all the business names have a closing date and the spreadsheet is sorted alphabetically by business name, so sorting by closing date, and using the method used in the topic " find the nth occurrence in excel", is not an option.
On sheet2, I would like to see ALL the business names that have a closing date in the respective month, as opposed to just the first. Then to the right of the business names I have the sum of all the amounts in that month, but I figured out how to get that one already.
View 2 Replies
View Related
Jul 14, 2008
I have several property codes in column E e.g THA 134,THB 224, C 122, 223 AD. The letters A, B, C, D denote a type of property but do not always appear in the same place in the cell (sometimes 3rd character, sometimes first character etc). I want to put in a formula in column F, so that if the character A, B, C or D is in column E then the result in column F will be "A type", "B Type" etc....
View 9 Replies
View Related
Mar 28, 2014
I am trying to solve problem with finding cells (all searching cells are in 1 column ) witch contains only recurring characters like : 00, aaaaa, 444444444 , etc.
View 3 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
Jun 6, 2006
For a given cell, I need to count the number of times a given character (e.g., the letter "a") appears. Is there a worksheet function or formula?
View 9 Replies
View Related
Jul 19, 2012
I have two lists of numbers.
The first list contains site numbers of people who havent responded to me.
The second list is the master list of site numbers along with a column showing the date they responded.
Now, a site number is built like this:
123456/0001
123456/0002
So it is possible for the same 6 digits to appear more than once in the master list.
What I need to do is to compare the first 6 digits in the non responder list against the master list, because some sites, like the example above, may have more than one '0001' tag and so if they have responded to me from site '0002' I dont want to spam their other sites with emails.
I've tried using match and various formulas I've found from google etc, but nothing seems to work!
The goal of this is to get a list of non respondents that have not responded from any of their sites listed in the master list.
Non Responses
Master List
Date of Response
203289/0001
201162/0003
06/06/12
[code]...
View 3 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
Mar 12, 2008
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
View 9 Replies
View Related
Jul 19, 2013
want a way to find out any special character in text file (.txt).
The .txt file I use is very large about 100 mb to 1GB. I need to find a way to write vba code that asks for input text file and the validates it and gives the message that following special characters are present in the file. Also, it gives their column number and row no's, where they are located.
The characters which needs not treated as special characters are numbers (0-9), alpha (A-Z) and special characters (@,-,%,$,+,=).
View 2 Replies
View Related
Dec 24, 2013
I'm getting an export from a CDR. This export contains the date and times people log on and off from a queue. For logging in they dial 511, for logging out they dial 512. They get a voice prompt and type their password. I need to know how much time they daily spend in this queue
At first this looks pretty easy. I just make a sum of all the times they called to 511, then a sum of all time they called to 512 and finally I substract those values and I end up with the correct time spend in the queue.
The problem comes when they call multiple times to 512 without actually logging off. For example, they type the wrong password or simply hang up.
Result is this in the CDR
FROM TO DATE TIME
101 511 23/12 08:34
101 512 23/12 11:58
101 511 23/12 12:34
101 512 23/12 14:45
101 512 23/12 14:47
101 512 23/12 15:00
The actual time spend in the queue is 5 hours and 50 minutes. But Excel calculates this as 35 hours and 22 minutes, because it counts the 512 values no matter what.
How can I make sure that Excel only calculates the values of they are either the last value in the row OR if they are preceded by 511?
View 2 Replies
View Related
Sep 1, 2008
I have a few thousand products codes (i.e ABCD123BLA08 or SHU267BLA) They are non standardised in length or structure. I wish to pull out everything upto the end of the third numeric digit.( ie ABCD123 and SHU267) I cannot use left as they are all different lengths. Ideally i would serach for a non numeric char after the number then use left up to that point. Search can't do this, FIND can't do this.
Summary
ABCD123BLA08 would be ABCD123
SHU246BLU would be SHU246
I147ORT08-12 would be I147
View 9 Replies
View Related
Nov 27, 2012
To all sifus out there, how can i transfer from these:
NAS517-3-2
-41353913
NAS517-3-5
NAS517-3-4
-42MS27253-2
-43353908
-44357182
To these:
NAS517-3-2
353913
NAS517-3-5
NAS517-3-4
MS27253-2
353908
357182
View 1 Replies
View Related
May 18, 2008
I have got a list of numeric abbreviations, for instance 10739011/21/31/41. What it should really display are the numbers 10739011, 10739021, 10739031 and 10739041 (the first six figures stay the same). All the numbers in my list are 8 figures long. I want to change the list from the list seperated by the backward slash to the complete numbers. I have uploaded an example of the list with backward slash between the numbers. Is there a way that Excel can automatically change these numbers to the full numbers?
Because all the numbers are 8 figures long, I thought the first 6 figures of the 1st number can be copied and those 6 figures pasted before the other two figures after the backslash. Auto Merged Post Until 24 Hrs Passes;sorry, pressed OK too quickly. The problem is that there are sometimes 4 numbers in the cell, sometimes 6 and once three. I would like Excel to complete all the numbers in the cell and then move on to the cell underneath it and so on. Also, I would like each number to have it's own cell.
View 5 Replies
View Related
Sep 20, 2009
I have a cell which will contain SER01+SER02+SER03
and what i need it to contain is [SER01]+[SER02]+[SER03]
and shocker is i've got this to work for the first instance but not the other two
code as below... be grateful for your help
Sub measure1()
Dim list As String, pos As Integer, refl As String, refr As String, newlist As String
list = Cells(1472, 16).Value
pos = InStr(list, "+")
refl = Left(list, pos - 1)
refr = Right(list, pos + 1)
newlist = "[" & refl & "]"
Cells(1472, 17) = newlist
End Sub
View 9 Replies
View Related
May 6, 2008
Need a formula/code that will determine what the corrected part number should be (insert dashes if they are missing) by comparing to other values in the list.
Original A1:A5 = {452, a-bc, 123, 4-52, abc}
Corrected C1:C5 = {4-52, a-bc, 123, 4-52, a-bc}
I can do this using an intermediate working column, but can this be done all in one formula, or via VBA?
My formulaic solution is thus: .....
View 5 Replies
View Related
Oct 14, 2006
I would like to lookup the last occurence of a particular cell value in a worksheet and use the cell value in the column immediately adjacent to this one in a calculation.
For example, The text "Run #" occurs several times in a sheet with a numerical value in the next column over. I would like to use the last occurence - or as far as that goes, the largest occurence of the numerical value. Unfortunately, there is additional data in between the "Run #" occurences, so a simple max() for the column will not work.
View 9 Replies
View Related
Jan 9, 2007
I am trying to extract the last occurence of ppp (4th column). So as long as 4th column is "ppp", i would want to extract the row wher the the last "ppp" occurence start and display in sheet2 "108-Jan-07vcdppp4". If data in 4th column is detected to be "ppp1", it would also extract the row wher the last "ppp1" occurence start and display in sheet2.
Note that the number of ocurrence is random and do not follow specific pattern. Pls see the attached for the example.
View 9 Replies
View Related
Feb 20, 2014
I have two sheets:
Sheet 1
Col A
A
B
C
D
Sheet 2
Col ACol B
AXX
BYY
AXX
AZ
DD
DD
I want to count the number of unique occurance of value in Col B in Sheet 2 as per value in Col A in sheet 1 and Sheet2.
My output result should be like following:
Result
Col ACount of col B value in Sheet 2
A2
B1
C 0
D1
View 3 Replies
View Related
May 26, 2014
I need to find out last occurrence of price (Last Date of Occurrence). Column A contains Date; Column B contains Highest Price of Equity Stock in that date. Reference price (Price we want to look up) is calculated in Cell E1, Cell E2 contains start of evaluation period (date), Cell E3 contains end of evaluation period (date). I want to find out the date on which "last" occurrence of Reference price in Column B "during the evaluation period" was found.
I have tried using array formula
{=MATCH(E1,IF(A2:A21>=E2,IF(A2:A21=E2,IF(A2:A21
View 5 Replies
View Related
Jun 13, 2002
I am trying to find a way to return an second answer for a second occurance using a VLOOKUP function. (maybe it can be done with something else?)
ex. range= A1:C3,
A1 = 40CA B1 = 30R2 C1 = TRUCK
A2 = 40CB B2 = 30A1 C2 = CAR
A3 = 40CA C2 = 30B3 C3 = TRAILER
VLOOKUP("40CA",A1:C3,3,FALSE) Returns "TRUCK"
How can I get a formula like this to return "TRAILER" the
second occurance of "40CA"?
View 9 Replies
View Related
Sep 16, 2008
[data] ....
I'm having a problem seperating the prices in Colomn H into three seperate cells C,E & G
The first figure in c is easy enough if a little long (any possible tidier solutions?)
The second Im not happy with it is clumsy using the fixed lenght figure "5"
The last I cant figure out probably because Ive done the second incorrectly!
Finally I can not see why figures apear in away team.
View 9 Replies
View Related
Feb 20, 2011
I have tried using the 'arbitrary lookup' function as listed here but can't seem to get it to work. [URL] .....
I have a list of all football fixtures and results in the premier league this season and I have a separate worksheet with a drop down menu to load each team.
What I am struggling to do is to get a function that will obtain the data corresponding to each of the home fixtures and the away fixtures of the team selected in the drop down menu.
I have managed to get the first result but do not know how to get the 2nd occurrence, the 3rd etc.
My formula for the first is
=INDEX(Fixtures!$A$3:$R$380,MATCH($E$1,Fixtures!$C$3:$C$380, FALSE),5).
I've attached the spreadsheet. (Unfortunately the format of the excel sheet can't be changed)
Forum Example.xls
View 7 Replies
View Related
