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.
View 13 Replies
ADVERTISEMENT
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
Nov 17, 2007
How I can change this formula to find the first 3 characters of a text string?
Instead of using numbers in column A, it will be Names, I need the user to enter only the first 3 characters of the name and then display the results, (Meaning that all others irrelaavnt rows will be hidden.
Sub myHidingRoutine()
Dim foundCell As Range
With ThisWorkbook.Sheets("Sheet1")
Set foundCell = .Range("a:a").Find(What:=.Range("a2").Value, After:=.Range("a2"), _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)
View 9 Replies
View Related
Aug 27, 2007
I have text string and would like two formulas to find,
1) the characters after the last space and
2)to find the number of characters after the last space.
View 9 Replies
View Related
Aug 28, 2007
I have text string and would like two formulas to find, 1) the characters after the last space and 2)to find the number of characters after the last space.
View 9 Replies
View Related
Feb 9, 2013
The problem - I'm trying to find a formula that will find text between 2 characters with unlimited instances in a single string and combine the found text to a single string. The character enclosing the text i'm after will likely be a % symbol. This formula would then be replicated down 5000-10000 rows.
eg. 1 - This is %an% example %sentence% to show what I %am% looking for
eg. 2 - This %is another% example of what I %need%
eg. 1 answer - ansentenceam
eg. 2 answer - is anotherneed
Ideally this would not use a macro as it will be applied to an ever expanding data set but I realize that may not be possible.
View 3 Replies
View Related
Aug 12, 2011
I've scoured the internet for this answer and have not found a formula that works.
I have a list of domain names/URLs in column A of workbook 1:
jhjh.com
hajfh.co.nz
123.123.12.12
I have a list of domain name extensions in workbook #2:
.com
.co.nz
.net
I want to determine if the cells in column A workbook one contain any of the strings in workbook 2 - note that I need to look at the string, not the entire cell in workbook one. If there is a match, true, if not, false for that cell within workbook one.
It's sort of a reverse array lookup with text. It seems that cross workbook and text is what causes the challenge.
View 9 Replies
View Related
Aug 6, 2008
I have a very large file of data, over 500,000 rows, opening in one sheet in Excel is not an option with my current version. In each row I need to change the characters in positions 41-44 from whatever they current are, to '9999'. I'm sure there has to be a way I can do this using vba, does anyone have a sample snippet of code, or another post they can point me too?
View 9 Replies
View Related
Dec 22, 2013
I have the following type of info in A1,A2,A3...
Code:
nameGaryage40cityPittsburgheight190
age30height150
ameLindacityMichigan
citySan Jose
ameHarryheight180age50
My goal is to get as close as possible to this,so it will be easier to sort and manage
Code:
nameGaryage40cityPittsburgheight190
nameLindaage30cityMichiganheight150
nameHarryage50citySan Joseheight180
I can't use the "" sign as delimiter to separate them into different columns because the age,city,name and height fields are in random positions on different cells.The good thing is person's name will always come after "name" string, age is alwals followed by "age" string, so it cannot be like nameheight40Michigan180
I think the following would be the easiest method(not for me tho).If on B1 I had a formula that said "find the string "name" and write anything after it until you reach the next "" character".On C1 field I could have a formula "find the string "age" and write anything after it until you reach the next "" character.On D1 I would have the same for "height" string,then on E1 for city string.
My question is somewhat similar to this one
Extract A String Between Two Characters
Formula which outputs the data between 3rd and 4th instances of the "_" character.Can we substitute "3rd and 4th" with a specific strings like "age" or "height" ?
Code:
=TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("|",SUBSTITUTE(A1,"_","|",3))+1,LEN(A1)),"_",REPT(" ",LEN(A1))),LEN(A1)))
View 3 Replies
View Related
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.
View 2 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
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.
View 3 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
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
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...
View 2 Replies
View Related
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 ?
View 1 Replies
View Related
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..
View 2 Replies
View Related
Feb 17, 2009
A simple question:
I have a column of numbers, for example:
2
5
5
8
8
8
20
50
I would like to know the value that corresponds to (for ex.) 5th position from, of course, the beginning of the column, ie 8.
View 2 Replies
View Related
Jul 18, 2006
What I have is a list of numbers, many of which are duplicated, in no particular order and I really want to avoid sorting. What I want to find is the lowest position in the list of a particular value. Unfortunaly MATCH only seems to return the first occurance of the value. Not really looking for a VBA solution.
View 8 Replies
View Related
Jan 9, 2013
I have in the first column something like this:
GLOBAL DESCRIPTION
GLOBAL DESCRIPTION1 Particular description1
GLOBAL DESCRIPTION1 Particular description2
GLOBAL DESCRIPTION2
GLOBAL DESCRIPTION2 Particular description1
GLOBAL DESCRIPTION2 Particular description2
I want to to have in the second column:
GLOBAL DESCRIPTION1
Particular description1
Particular description2
GLOBAL DESCRIPTION2
Particular description1
Particular description2
So far I have this: +IF(EXACT(A1;UPPERCASE(A1));A1;+RIGHT(A1;LEN(A1)-(FIND(X;A1;1)-1)))
X standing for the position of the first lowercase, which I don't know how to get.
View 9 Replies
View Related
Jan 15, 2009
In the following code, you will find a sorting function with a range is hard coded. Instead of hard coded value which is marked with red colour, i need to find that range value dynamically. How to write a code to find that ?
View 2 Replies
View Related
May 7, 2012
I'm messing around with two shapes, a Cannon and Cannon Ball. With code the Cannon rotates for the angle of fire. What I can't do is position the Cannon Ball at the end of the Cannon when Cannon is at an angle. Long time since I was in school so forget most of my maths.
Code:
Sub IamUseless()
Rem this is work in progress and in NOT WORKING!!!!!!!
Dim xDistance As Double ' is inital x position of object
[Code].....
View 1 Replies
View Related
Feb 14, 2013
I have a user form where two command buttons are set with the following code:
Code:
Private Sub CommandButton1_Click()
Frame1.Visible = True
Frame2.Visible = False
[Code]....
Now, when cmdbtn1 is clicked, frame1 is visible and vice-versa.
However, the position of both the frames on the user form are not on the same position on the user form. Indeed, it takes the position of the design view.
I want a code which will place the frame1 and frame2 at the same place and position on user form, irrespective of its position in the design view.
View 2 Replies
View Related
Mar 18, 2009
*ABCDEFGHIJKLMn8OPQRSTUVWXYZAAABACADAE1n1n2n3n4n5n6n1n2n3n4n5n6n7n8n9n10n11n12n13n14n15n16n17n18n19n20n21n22n23n24Position 24567141517192223272834353639404143464748493;223268103048456714151617192022232830343637383941434446485;16;20:24421432364148456791014151718202328303438394041444647485;9;2159173537384645679101416182023273032343839404144474849620253542454645679101416182327283234353738394043444647497131423414748567810141617202528303435363839414244454647
I have selected numbers to play in the table shown above from cell G2:AD7 and celebrated draw result numbers are shown in cells A3:F7
Now, suppose I want to play the numbers, which are in cell G2:AD2 and the draw-celebrated numbers are shown in the cell A3:F3.
Now I want to check cells A3:F3 with G2:AD2 and want to know in which position are the celebrated draw matched with the numbers played.
In this case the result in AE2 will be 3; 22 that means only two numbers are matched with the played numbers. Those numbers are the number ‘6’ which is in the position 3 counting from G2 and number ‘48’ which is in the position 22 counting from G2 again.
The result must be shown in column AE for all the draws as shown in the table above.
View 9 Replies
View Related
Feb 17, 2014
i have 8 digit invoice numbers. Each 8 digit number starts with one of three 4 digit ID codes. Depending on the first 4 digits, I want a different "company" to show up.
First seems to work fine.
=IF(E6>"1NCC","North Carolina",IF(E6>"2ALA","Alabama",IF(E6>"3SCC","South Carolina")))
View 3 Replies
View Related
Feb 24, 2014
I have variable length strings, as shown in the sample cells below
TopographyTAB_FormatSP9200
TopographyTAB_FormatSP9400
OS_DataLandline
ITNTileTabTQ2286
I want to select any record where 5th and 6th character from the right hand end of the string which = either "SP" or "TQ". Where this is the case I'm then looking to truncate the string to remove the last 7 chacters.
None of what I have so far found using mid or right string appears to be particularly applicable.
View 9 Replies
View Related
Dec 26, 2009
I'm trying to remove everything after a specific character in a string.
I.e. change a website address to the hostname
http://www.excelforum.com/newthread.php
http://usa.excelforum.com/forum/new
to
excelforum.com
usa.excelforum.com
I'm using this formula, which strips the http:// and the www., but does not replace the characters after the first remaining "/" as the wildcard is not recognized.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"www.",""), A2,"http://",""), A2, "/*", "")
View 11 Replies
View Related
Feb 11, 2009
Replace characters in a string. I have this macro:
View 2 Replies
View Related
Feb 21, 2010
I'm importing data from the internet. The cell I'm trying to get is B8 on "Temp_Data" onto cell E3 on "Other Data". When it imports the cell into Temp_Data for some reason the cell expands, and I can't have that. I would manually copy and "Paste Special", text, into the proper cell, and that works. I can also copy the fist 7 letters in the string from B8 and paste them into E3. My code currently looks like this:
View 2 Replies
View Related
Aug 21, 2012
I'm having difficulty figuring out how i can Trim the last 3 characters from this string
Code:
FeatureCode1 = Split(errorChecker.lstErrorMessage.List(0), " [")(0))
I tried
Code:
FeatureCode1 = Left(Split(errorChecker.lstErrorMessage.List(0), " [")(0)), 3)
But that only SHOWS the last 3 characters... how i can just cut off the last 3 characters?
I'm guessing something to do with TRIM, but not sure how to implement it?
View 3 Replies
View Related
