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?
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?
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?
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)
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?
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 View Related7900 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.
I have some cells with some spaces and then text where I want to find the position of the first letter. Do any of you know about any function I can use?
The example below contain 5 spaces and then my name. It should give me the answer 6.
" Magne"
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.
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.
Let's say we have a text string in the excel cell: 12.123.12.1
Is there any way to get the latest dot's position using excel's functions?
How to find and show position number? I try merged two function, but doesn't work
1º=FIND("0";B2)
2º=FIND(CHAR(1);SUBSTITUTE(B2;"0";CHAR(1);2))
Number
Position
[Code].....
I understand that the function FIND(find_text, within_text, start_num) will find the first occurrence of a stated string. And by adjusting the start_num, you can skip a set number of characters before the search begins.
But, is there a way to find the Last occurrence of said string? In my case the find_text string may occur multiple times in the variable length within_text cell to be searched.
Sample: Find the last occurrence of MDU
Code:
Sample string1 - ABDJ FFU MDU WWW AEH JJF MDU JJI LLK OOI HAHA QWAS
Sample string2 - HFUR MDU HDS IGH RJR KDHF LLDE MDU KKJ MDU GGG
In the above 2 sample strings the output of the Find() function needs to be 26 & 40. Having a cell formula would be desirable, but if it can only be done by VBA that is acceptable, also.
I have multiple automatically updating data sets and I need to extract certain data to perform calculations on, this is the format of the data
SPLIT AB H 2B 3B
HOME 20 6 1 0
AWAY 20 7 2 0
SPLIT R HR RBI
HOME 2 0 1
AWAY 3 1 4
basically, what I need is to extract the data for home and away, and put them each on one line, no problem if this is always the format, but there are other lines of data that I don't need thrown in there at times which shift the data up or down
a simple vlookup will extract the first occurance of home, but I need another formula to extract the second occurance so I can have them all on one line and have the data as:
SPLIT AB H 2B 3B R HR RBI
HOME 20 6 1 0 2 0 1
AWAY 20 7 2 0 3 1 4
I need to find the first occurrence of the word grandsal after each employee details thru VBA. Is it possible to do that. If yes how to construct the if loops.
I take this time to thank all the members of the ozgrid for their exceptional.
I'm just new here as well as in Excel-VBA stuffs. I will appreciate if someone could help me. Here is the scenario. I have a data piled in a single column and different rows. Like this one:
spot.return
23,54,56..
532,677,755...
..
..
..
876,989,999;
spot.return
54,58,60..
..
..
spot.return
I need to select and copy all rows in between the occurence of "spot.return". There are a total of 80 occurences of "spot.return".
I am running EXCEL 2007 on XP. What is the EXCEL cell formula to put in a cell (for example"E5") that will reference the "last" occurrence of a specific text string in column A (For example Chard ) but will show its corresponding column B content (for example its PH number 3.45) regardless of the number of data entries that will be inputted in the future for both column A and B.
A B C D E
1WINEPH
2Chard3.24
3Merlot3.36
4Cab 3.44
5Merlot3.38 xxx
6Chard3.26
7Chard3.45
8Cab 3.41
9 Merlot3.33
10
I'm trying to extract all the numbers from the left of the delimiter ":" . They can occur once or more.
E.g. cell F2 contains BP2.2.1:40 BP2.2.1:50 BP2.2.1:60 BP2.2.1:70 BP2.4.1:80
and what i want as a result is : BP2.2.1 BP2.2.1 BP2.2.1 BP2.2.1 BP2.4.1.
I've tried =IFERROR(LEFT(F2,FIND(":",F2)-2), " ") but only displays 1 out of a possible 4 in the cell.
Is it possible to find the location of different characters in a string (using VBA)? Normally I would use InStr of InStrRev option in VBA but my situation is a little more complicated.
The character I am looking for is not always the same, sometimes it is a , or a . or a : or a "blank" etc
I tried to do it like:
dim strChar as string
strChar = "[,.? /]"
SearchPosition = InStr ("cell location", strChar) (searchposition is the name of the function I am trying to make)
This works if I define only 1 character, this way it gives me a 0 as outcome
I have tried to change it to strChar = "[,]" or strChar = "[.]" or strChar = "[ ]" but this doesn't work.
I have a text string in a cell, something like:
000024AC1011.
I need to find a way to find the first nonzero character in the string, and what position it is in. For instance, in the above example, 2 is the first non-zero character, so the function would return 5, since the 2 is the fifth character into the string.
why this code only works till the first assignment?:
Dim pos As String
Sheets("Sheet1").Select
pos = Range("A1"). Find("John").Address
pos = Range("A1").Find("Mary").Address
pos = Range("A1").Find("Peter").Address
In the second assigment
pos = Range("A1").Find("Mary").Address
I get a "Object Variable or with block variable not set" error message.
I have been confronted with an issue that has bugged me: Now do I extract a part of the string according to lets say "markers"?
Here is what I mean:
I use the code below to get the bottom range of my tables:
Code:
blimit = Activecell.Address
I haven't had problems because my tables were small, then I would:
Code:
blimit = Right(blimit, 3)
If the table was small there was no problem, since lets say the Activecell.Address = "$C$123", resulting in blimit = 123.
My how do I extract a part of the string, knowing I have 2 "$" ? That way I would make a version of this that always works.
I cannot use FIND or any formulas I am aware of since they all start counting from Right to Left... I thought of using Replace() to get the first "$" and change it for something else like "-" then looking for the second "$". But Replace() basically changes both signs... making my idea... well obsolete...
I have a choice of 9 command buttons on a form that enter a short string of text into a textbox. Each button enters a different string into the textbox. The string is the prefix to a product serial number. Once the user has clicked the button & entered his preferred prefix I would like the cursor to be flashing after the last character ready for the user to manually type in the remainder of the product serial number. I'm guessing (with my limited ability) that I have to create a function to do this & then call the function when the button is clicked ?
View 1 Replies View RelatedNow do I extract a part of the string according to lets say "markers"?
Here is what I mean:
I use the code below to get the bottom range of my tables:
Code:
blimit = Activecell.Address
I haven't had problems because my tables were small, then I would:
Code:
blimit = Right(blimit, 3)
If the table was small there was no problem, since lets say the Activecell.Address = "$C$123", resulting in blimit = 123.
My how do I extract a part of the string, knowing I have 2 "$" ? That way I would make a version of this that always works.
I cannot use FIND or any formulas I am aware of since they all start counting from Right to Left... I thought of using Replace() to get the first "$" and change it for something else like "-" then looking for the second "$". But Replace() basically changes both signs... making my idea... well obsolete..
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 RelatedI 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 RelatedI 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
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
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 RelatedHow 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