Search String For Defined Set Of Characters
Nov 21, 2009
I have about 700 cells I want to interrogate. Within each cell the following text appears “Estimate – BOLB/02/1234 – Some more text here”. I want to search all the cells and delete the cell contents but leave the “BOLB/02/1234”.
The problem I have is that the string is different in each cell but the format is the same. It always starts with BOLB followed by /, followed by 2 numbers, followed by / followed by 4 numbers i.e. BOLB/**/****.
View 3 Replies
ADVERTISEMENT
Nov 24, 2006
I want to search a string for specific characters.
f.e. Begin = "bfPaa2"
I want to look for "P"
So, the answer has to be: Letter = "P" after searching the string
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
Apr 11, 2013
I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.
I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.
View 6 Replies
View Related
Apr 5, 2013
I import some text from a web page. The data is separated in rows and columns so it easily slip into cells in a nice row and column manner.
Now, I want to parse some names in one column (column A:A). The names do not reside in any other column. I want to change the content for those cells that is "Alpha Beta" into just "Beta", and I only want the replacing to be carried out on a specific sheet (named "Import").
I've recorded a VBA script and it look like this:
Cells.Replace What:="Alpha Beta", Replacement:="Beta", LookAt:=xlPart,
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
But, when I try different setting for the range I get the same script, I mean, for when I try the search & replace with settings for both the whole workbook and for the current sheet.
How can I control the search & replace with VBA to only be valid for a defined sheet?
View 5 Replies
View Related
Mar 19, 2013
I am trying to lookup if a string like a name exists in another string which has a buch of values for example to see if andy exists in a string which has data like andy;sandy or sandy exists in andy;sandy
View 9 Replies
View Related
Nov 13, 2006
I have a sheet A1:R456 provided from an outside source, with many large text entries. In some of them appear various ascii strings such as, but not exclusively: •
It appears these have replaced apostrophe's, dashes, and other types of punctuation and formatting (bullets, etc).
I have used the Find (Ctrl-F) to locate some, but it only works if you know what you're looking for. According to Excel HELP! :o Clean() only strips the lower 32 ascii codes, and I'm pretty sure these are at the high end of the ascii chart.
I don't want to remove them, I want to replace them, but I'm not sure if it should be an apostrophe, a dash, or whatever. So I'd be happy to find them and I'll fix it as best I can.
Any VBA code to locate all cells in a range that contain any high-order ascii (say above 127) and fill with yellow?
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
Dec 30, 2008
I am trying to count the characters in a string in one cell. For example, I want to count the number of "-"'s in the cell. The version number of part numbers is always the last two characters if the value contains at least one "-". However the parts have a non standard number of characters.
444444 = 0
444-44 = 1
44-44-44 = 2
There can be 0,1 or 2 "-" in the string. I can use the substitute formula to get the root part number, but I was wondering if there is an easier way. The raw data is from our ERP system, so I multiply the value by 1 to get a number if there are no "-" for the vlookup in another formula to work....
View 9 Replies
View Related
Dec 22, 2009
Is it possible to format a character in a string using vba?
For example if i had a string of GGGFF, and i wanted to make the G's appear as Green font and the F's as Red font.
View 9 Replies
View Related
Nov 16, 2006
I have a string like: AAJDGYE030000460. How can I remove the first character in a macro? I need to look at the second,third, and forth character
View 3 Replies
View Related
Dec 21, 2006
I have a set of data in column a that consists of email addresses. These email addresses all have underscores after them, ie "abc@hotmail.com_______". It will be a different amount of underscores everytime and I don't want underscores to be removed that are actually part of the address. I had been using the find replace function through vba, ie
Range("A:A").Select
Selection.Replace What:="_", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
however this ofcourse removes from actual parts of the email address. Is there a way to do this?
View 6 Replies
View Related
Apr 11, 2007
I have a string, specifically a file path, and I want to take the last 5 characters of the string. How would I got about doing this?
View 3 Replies
View Related
Jan 22, 2008
I've come across multiple times where I have to do this same sort of task, and I don't think I am doing it the most efficient way.
What I need to do is take a variable which holds a string and remove the last 9 characters from it. Don't need to know what the last 9 characters are, all I care about is knowing what the other characters are in the string. I know this can be done through thingslike susbstrings, but I don't think VBA has a substring function.
View 3 Replies
View Related
Mar 25, 2008
I want to create a user defined function to search a column of data for a part number.
If it exists I want to have a the UDf returna "fail" otherwise "pass"
Here is the code I was trying to use
Function firstpass(SN As String) As String
ws = Worksheets("Defects")
c = ""
With ws.Range("a1:a9999")
Set c = .Find(SN, LookIn:=xlValues, lookat:=xlWhole)
End With
If Not c Is Nothing Then
firstpass = "Pass"
Else
firstpass = "Fail"
End If
End Function
This function only returns a "#value" and I don't quite know how to troubleshoot it.
View 9 Replies
View Related
Oct 14, 2009
I have a worksheet that I produced from optical character recognition, and there are a lot of funky characters that I need to get rid of. One is a line break or carraige return (I assume it's the same character that I could insert by entering ALT+ENTER). How do I search for this special character to replace it? How do I identify what that invisible character is?
View 2 Replies
View Related
Dec 20, 2007
I have a worksheet with a list of ID Numbers:
for example:
A1001
A1002
A1003
:
etc,
I purchased the book from Mr.Excel and got it last week called VBA and Macros for Microsoft Excel. I can't seem to find the code for macros to search through the cells and then either change the cell colour to highlight the cells that contains an alphabetical character. I am trying to remove all the characters in the cells so only the numbers are left over.
View 9 Replies
View Related
Jun 20, 2007
I have done a search on carriage returns/characters and what has been suggested is not working. I have BOTH carriage returns in the cell and also the boxes (that I assume are also carriage returns. I tried the substitute formula (=substitute(cellref,char(10),)) in excel but it only removes the alt+enter carriage return. I tried the various suggestions of find & replace but the chr$..etc did not work! Is there a formula to clear both in one (not a macro!)..?
View 2 Replies
View Related
Oct 24, 2012
I want to get only unique characters in a text. For ex. ozgrid.com. I should get ozgrid.cm (o is already came). another one. For ABCEABC the result will be ABCE. So I have written an UDF. Unfortunately there was "Argument is not optional" error in "UNIQUECHARS = Join(JoinT.Item)".
How could I get rid from this error and get my result?
Below is my code.
VB:
Public Function UNIQUECHARS(chtxt As String)
Dim c() As Variant
Dim JoinT As New Collection
Dim sp As String
cchtxt = Application.WorksheetFunction.Substitute(chtxt, " ", "")
[Code] ......
View 3 Replies
View Related
Aug 2, 2006
I am trying to remove all characters that aren't letters or numbers from a string. Is there any way to differentiate between a non-alphanumeric characters and alphanumeric characters? I'm thinking of something like "ISTEXT()" that I could use on one character at a time. Or are there any wildcards I could use in the Replace function?
View 9 Replies
View Related
Dec 23, 2008
I'm using Excel 2007. I want to write a formula that will list all characters in a string to the left of a "-". See below example. What formula would I use?.......
View 2 Replies
View Related
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
View Related
Dec 23, 2009
I have a problem that I just don’t seem to have the brain power to solve right now. I have a list of websites and I need to extract all characters after the last “/” in the URL. URL example:.....
I have been using the find function in conjunction with LEN and RIGHT etc but the multiple instances of “/” is causing headaches for me. Also, the trailing string of characters that I want to extract can be both text or numeric and are of differing lenths.
View 3 Replies
View Related
Jan 30, 2013
I haven't had the need to work with partial strings till now and having difficulty finding the right context in other threads. I need to put the first 4 characters of one cell into another cell. The line in the below code with the comment is the one I need. It's the only one where I need only part of what is in the cell.
It should be = the first four characters of cells(zRow, "A")
Code:
Dim LastRow As Long
Dim zRow As Long
Dim cRow as Long
LastRow = Sheets("Datasheet").Range("N65536").End(xlUp).Row
zRow = 1
cRow = 2
[Code] ......
View 1 Replies
View Related
Oct 10, 2013
I have a sheet of about 10k rows that I need to switch some things around on. The string is below:
Acura|TL|1995^^Acura|TL|1996^^Acura|TL|1997^^Acura|TL|1998^^Acura|TL|1999
It needs to say:
1995|Acura|TL^^1996|Acura|TL^^1997|Acura|TL^^1998|Acura|TL^^1999|Acura|TL
How to fix this with a macro or formula. The Make Model and Year on each string will be different. Basically I need the format to be Make|Model|Year.
View 9 Replies
View Related
