# Position Of Last Occurrence Of Character In String

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?

ADVERTISEMENT

## Find Last Occurrence Of Character In A String

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?

## How To Find Nth Position Of Character In A String

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?

## Last Numeric Character Position Of A String

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)

## Finding Nth Position Of Character Within Text String

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?

## 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.

## Using FIND To Locate Second Occurrence Of Character

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.

## Position First Character

Mar 6, 2009

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"

## Remove / Replace ONLY FIRST Occurrence Of First Character In All Rows Of Column?

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.

## Excel 2003 :: How To Remove Characters To The Right Of Last Occurrence Of Special Character

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.

## How To Get Latest Position Of Certain Character In Excel Cell

Aug 6, 2013

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 Show Position Of Specific Character In Cell

Mar 9, 2014

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].....

## FIND LAST Occurrence Of String

Nov 12, 2013

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.

## Vlookup To Return Second Occurrence Of String

Mar 14, 2007

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

## First Occurrence Of A Word After The Searched String

May 8, 2007

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.

## Search For A String. Then Copy All Cells Between Each Occurrence

Apr 4, 2007

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".

## Excel 2007 :: Reference Last Occurrence Of Text String

Feb 8, 2011

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

## Extract Number From Multiple Occurrence Delimiter Of String Excel

Aug 14, 2014

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.

## Find Position Of Various Characters In String

Jul 11, 2014

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.

## Searching Through A String For Position Of 1st Non-Zero Digit

Sep 1, 2009

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.

## Position Of A String In A Range Of Cells

Apr 24, 2006

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.

## VBA - Extract Part Of A String According To Position / Markers

Sep 11, 2012

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...

## Position Flashing Cursor At The End Of String In Textbox

Oct 15, 2013

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 ?

## VBA - Extract Part Of String According To Certain Position / Markers?

Apr 10, 2014

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..

## Looking For 'X' Or 'Z' As The 5th Character In A String

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.

## Nth Character From String

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?

## Searching String For A Character

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

## Assign Value To Character Or String?

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

## Evaluate First Character Of String In VBA

Feb 11, 2010

How can I evaluate just the first part of these stirings so I can just do something like Left("Ca",2)?

## Splitting Character String

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

Copyrights 2005-15 www.BigResource.com, All rights reserved