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 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?
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)
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.
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%
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.
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?
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" ?
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.
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?
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 ?
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..
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.
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.
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 ?
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
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.
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.
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.
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.
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.
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:
