Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Search String For Defined Set Of Characters

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Search A String For Specific Characters
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 Replies!   View Related
String Search - To Create A Combobox To Filter A Set Of Data By The Month That Is Entered
I am trying to create a combobox to filter a set of data by the month that is entered.

The below code worked fine when there was just the month entered, but now all the entries in the sheet are in the format 01 January 2009. So I need a section of code which will search for the combobox value as part of a string in my range.

The cbodate values are Jan, Feb, March etc ....

View Replies!   View Related
Set Bold To Font On Another Worksheet: Application Defined Or Object Defined Error
Call movedata(1, rrow, ecol, erow)
'Contact Person
Call movedata(26, rrow, ecol, erow)
Worksheets("new"). Range(Cells(erow, ecol), Cells(erow, (ecol - 2))).Font.Bold = True
Call movedata(2, rrow, ecol, erow)
'Street Address
Call movedata(3, rrow, ecol, erow)
Call movedata(4, rrow, ecol, erow)
' zip
Call movedata(5, rrow, ecol, erow)
Worksheets("new").Range(Cells(erow, ecol)).Font.Bold = True
' speed dial
Call movedata(6, rrow, ecol, erow)

the first time font.bold is set to true, it completes w/o error. The bolded line returns 'application defined or object defined error 1004'. Ive tried activating the new sheet immediately before setting bold (like the first time it gets set) but it still errors.

View Replies!   View Related
Extract Set Of Numeric Characters
I have a column contains Telephone numbers with or without area code and country code for across the country (India). I need to extract the telephone numbers alone (neither area code nor country code). Telephone numbers will be 6 or 7 or 8 digits (not more than that). Is it possible to extract any set of numbers contain 6 or 7 or 8 digits continously? Some of the cases contains 2 contact numbers (2 set of 6 or 7 or 8 digit characters, between special characters will be there like slash, comma, space, hypen, etc.,

Here are some examples: ....

View Replies!   View Related
Remove Characters From Right Of String
I'm trying to remove everything after a specific character in a string.

I.e. change a website address to the hostname


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 Replies!   View Related
Formatting Characters Within A String
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 Replies!   View Related
Collect First 7 Characters Of A String?
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 Replies!   View Related
Count Characters In A String
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 Replies!   View Related
Replace Characters In A String
Replace characters in a string. I have this macro:

View Replies!   View Related
Remove X First Characters In A String
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 Replies!   View Related
Remove Last X Characters From String
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 Replies!   View Related
Taking The Last 5 Characters Of A String
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 Replies!   View Related
In Search Of ASCII Characters
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 Replies!   View Related
Remove Non-alphanumeric Characters From A String
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 Replies!   View Related
Find The First 3 Characters Of A Text String
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 Replies!   View Related
Adding Space To A String Of Characters
I need to add spaces to a string of characters so that it is readable, the database is huge so I cannot do it manually:

For example,
123456John Doe 3456Adam Sharp I need it to apperar as:
123456 John Doe 3456 Adam Sharp

but still remains at the same column. Is there a quick way to do this ?

View Replies!   View Related
Identify BOLDED Characters In A String
I have many text strings (sentences of text) in the body of an Excel file. In some of those strings there are bolded words. I want to search through those strings for the bolded characters so I can extract those words and go on from there.

I know how to set up the search across the rows and columns and how to step through the strings for the bolded characters. (I'd just "brute force" my way by counting through the strings one character at a time). I just don't know what the search criteria would look like.

View Replies!   View Related
Pull Out Text Between 2 Characters In A String
I'm trying to pull text out of a middle of a long string of data located within one cell. The string is not always the same size and the text I need to pull out is not always in the same spot

For example - in this string

source=google&adgroup=Jimmy Bop (Broad) >>/yes/klingon/GetStarted.aspx >>/finace/success.aspx?

I want to pull out the text "Jimmy Bop (Broad)
It will always be between "adgroup=" and ">>/" (although sometimes the character ">>/" will appear multiple times within the string.

View Replies!   View Related
Formula To List Characters In String
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 Replies!   View Related
Inserting Characters Into A String Of A Certain Length
I need to insert 00 if the string of text = 13 in a specific column.

eg. Column K can contain any amount of lines of data. The data will either be 15 or 13 characters in length. If its length is 15 its fine, if its 13 I need to get it to 15 characters.

The first 6 characters are letters so if the length is only 13 I need to add the 00 in after the last letter.

View Replies!   View Related
Extracting Texted Characters From A String
ive search the forums for this particular need and found somthing close but not what i need. i need to write a formula (not VBA) to extract a string of letters which can very in number out of a string of characters in a particular cell.

ex. cell A1= 00abcdefg00-000-00
cell A2 = abcdefg

i've tried different methods but can seem to come up with anything.

View Replies!   View Related
Removing Characters From A String Variable
Let us assume x and y are strings. Now let us make x equal to a small sentence (ie. full of words and spaces). I now need to somehow make y equal to x, but with the last word removed. I need to do this in VBA.

View Replies!   View Related
Remove Characters From End Of String Only
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

Selection.Replace What:="_", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _

however this ofcourse removes from actual parts of the email address. Is there a way to do this?

View Replies!   View Related
Search Through Cells With Alphabet Characters
I have a worksheet with a list of ID Numbers:

for example:


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 Replies!   View Related
Search And Replace Special Characters
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 Replies!   View Related
Search On Carriage Returns/characters
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 Replies!   View Related
Create A User Defined Function To Search A Column Of Data For A Part Number
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"
firstpass = "Fail"
End If

End Function

This function only returns a "#value" and I don't quite know how to troubleshoot it.

View Replies!   View Related
VBA How To Select Specific Characters From A String
In one column I have different objects separated by a comma. I need to select one of these: 11,20,30,60,61 and copy it into another column. I have used this
For counter = 0 To not_empty_cells

For counter_dep = 1 To 5

position = InStr(1, (Cells(counter + 3, 4)), department(counter_dep))

If position > 0 Then
symbol_dep = Mid(Cells(counter + 3, 4), position, 2)

Cells(counter + 3, 10).Value = symbol_dep
End If

Next counter_dep

Next counter

It works, however, once in the first column there are the following objects: 60,6128,CZ, it takes 61 but it should take 60. Unfortunately, the position of the object can vary, it is not always on the first position.

View Replies!   View Related
Strip Unwanted Characters From Text String
Is there an Excel function that can strip a specificed unwanted character from a text string to leave all the other characters excluding the unwanted one?

e.g. if the original text strings were (in separate cells within a workbook):


The function would strip the "-" to leave:

N7D ...etc.

View Replies!   View Related
Four Random Numbers Added After The Four String Characters
I know the randbetween portion of my code will not work - but how can I make this work with VBA? I need four random numbers added after the 4 string characters.

For Each R In MyNewR
If IsEmpty(R) Then
R.Value = Right(R.Offset(0, -1), 4) & _
Application.WorksheetFunction.randbetween(1, 9)
End If
Next R

View Replies!   View Related
Formula To Find Characters After The Last Space In A String
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 Replies!   View Related
Extract The First 5 Characters In A String, And Convert Them To A Number
Say I have a string "09800EBHR052708"

How would I take the first 5 characters and get a number out of them?

View Replies!   View Related
Extract Trailing String Of Characters From A URL
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 Replies!   View Related
Count Number Of Characters In String/Text
If 'a' is a string that contains x number of characters, how do I find out what x is (in VBA)?

View Replies!   View Related
Search For Specific Characters In A Range Of Cells
I have a column of values of peoples initials and I want to cound how many times one persons initials occur in those cells. I can do this if the cells just contain one person i.e. "CH" or "DH" but if the cell has two or more peoples initials i.e. "CD DH" I can't do it as it only matches the exact search criteria.

How can I total the numebr of times a required set of initials appears in a specific column of cells? Not bothered is it requires a macro or a formulae, just can't seem to figure this one out.

View Replies!   View Related
How To Detect When # Of Characters In One Column Does Not Match # Defined In Other Column
how to create a macro that:

- In each row, pops up a Warning Dialog Box if the amount of letters in column "J" doesn't match the # defined in column "B"?
- Preferably this would pop up the Dialog Box as soon as the error occurs (so it's always running)

Two Exceptions:
- Does not look at Rows 1 & 2
- Does not look at Rows where column B = 0 or null

View Replies!   View Related
Include/Exclude Specific Instring Characters From A Querry Search
I have a querry- file search application macro which searches all directories and subdirectories found within the provided string path and brings in specific data from spreadsheets in those directories. I would like to have the ability to exclude some of the subdirectories which are currently being querried.

I'm wondering if it is possible to add additional code that will allow me to either include or exclude specific directories being querried.

Here's an excerpt of the ....

View Replies!   View Related
Use String To Set As Commandbutton
I have a large number of commandbuttons on a userform that I wish to apply captions to via a for loop. Each button is numbered appropriately for this purpose. e.g.

Button_1, Button_2 etc etc

I want the loop to compile a string that represents the button name e.g.

String = "Button_" & Loop_Count
and then use my defined commandbutton:

Dim My_Button as commandbutton
to name the buttons. So the loop would be something like:

for Loop_Count = 1 to 100

String = "Userform_Name.Button_" & Loop_Count
set My_Button = String
My_Button.caption = "Test_" & Loop_Count

next Loop_Count
Unfortunately trying to set 'My_Button' using a string like this doesn't work.

View Replies!   View Related
How To Get Set Of Chars From A String To A Variable ..

This one has two sets of quotes, with the information from the noted post I can retrieve the string "S5a09b-90", now that I have a double set of brackets "( )" how do I get the value for the second set of brackets, "CAL_15"?

In the quoted string, CALIB/SENS,S, is constant. The first set of brackets are constant (string length is not), the second set of brackets are constant (string length is not).

View Replies!   View Related
Set A Workbook Variable As A String
I have a string variable contacting a file.

I want to set a workbook variable to this file, WITHOUT actually opening it!

DIM wb as Workbook
DIM filenm as String

filenm = "C: empdata.xls"
set workbook = filenm

So I know this won't work, but is there anyway around it? (bearing in mind I don't want to open the file!

View Replies!   View Related
Get Set Of Chars From A String To A Variable
I believe I should use the INSTR function, but I simply dont understand its use very well. I have a string in a cell, "MEAS/SPHERE,F(QUA_1),5", the characters I need start at the "(" and end at the closing ")", ref: (QUA_1). The start and end will always be the same, the length of characters and the spelling of characters be anything. Thus, QUA_1 can be CAL_1, CAL_2, SOMEOTHERTEXT_1 or 187bgo_199, etc, always enclosed in "( )". I wish to place the characters and the closing "( )" into a variable as a string.

View Replies!   View Related
Search For Sub String
I have a column with text that can contain a number of words one of which is 'UK & Europe'. I want to have another cell which will contain the value EMEA if the the cell mentioned above contains the word UK. how to do this as I'm stumped... I get a #value in cells that dont contain the word when using the formula =IF(FIND("UK",H27),"EMEA","ROW")

View Replies!   View Related
Automatic Search Of Data Set
I'm kind of rusty with spreadsheets and Excel 2007 is entirely new to me. I'm not even sure what I'm trying to do would be called.

I have a spreadsheet that is a list of records; a name, ID number, one text, and four numeric columns per record.

I would like to make a set of buttons or something that will automatically do a custom sort. Basically a "sort by this criteria, sort by different criteria" etc. so I don't have to manually do the sort repeatedly.

View Replies!   View Related
Search For All Instances Of A String
I'm using the following code which is working well. It finds an instance of the string "A83". Once it finds it, it moves down x number of cells and populates it with a new string. It then moves down another x number of cells and populates it with another new string, etc.

The problem is, it is only looking for "A83" one time and performing the appropriate actions at that point. What I need to do is look for *every* instance of the string "A83" and perform the appropriate actions.

View Replies!   View Related
VBA - Search For A String
A1:A2000 (or the wole column) will have the following:

Black Total
Grey Total
Total Grey Print
Blue Total Print
and so on.

Each cell will contain the word "Total". In B1:B2000 ( or the whole column) I would like to remove the word total from the column A. Example:

B1 should only contain Black (instead of Black Total)
B3 should only contain Grey Print
B4 should only contain Blue Print

View Replies!   View Related
Search A String Within A Sheet
I have a table of data and i need to search the table for 8digit numbers, that are beginning with "2" and copy the numbers to the column on the right. A cell can contain multiple numbers with 8 digits.

View Replies!   View Related
Search String In Column
I have this sheet: BEFORE MACRO..............

MACRO RESULT..............

Macro descripion:

Column A ( SEARCH STRING) list the string to be searched in C column
Column B (RESULT) reports the macro result
Column C ( List ITEMS ) is the column where the macro have to search the string

- Starting from A2 up to the end of value in A column
- Search the substring ( A2 cell value ) in C column starting from C2 * if found copy all the C row content in B2

If 'not found' write NOT FOUND (*) all search ( from A2 to An ) starts always from C2

View Replies!   View Related
Search For String Within Cell
I use the following code to search through a database and paste the results in a separate worksheet.

Private Sub TextBox8_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
sheets("Search Results").Unprotect
Dim cell As Range
Dim foundcell As Boolean
If KeyCode = 13 Then
Application. ScreenUpdating = False
Application.enableevents = False
If Not TextBox8.text = "" Then
For Each cell In sheets("Detail").Range("e2:e" & sheets("Detail").Range("e65536").End(xlUp).Row)
If cell.Value = TextBox8.text Then
foundcell = True
With sheets("Search Results").Range("e65536").End(xlUp).Offset(1)
.Value = cell.Value
.Offset(, -4) = cell.Offset(, -4).Value
.Offset(, -3).Value = cell.Offset(, -3).Value
.Offset(, -2).Value = cell.Offset(, -2).Value
.Offset(, -1).Value = cell.Offset(, -1).Value
.Offset(, 1).Value = cell.Offset(, 1).Value ................................

View Replies!   View Related
How To Align (set Tabs Or Columns) On A Text String
How To align (set tabs or columns) on a text string ....

View Replies!   View Related
VBA Set ODBC Connection String For Query
My workbook has 9 queries, all needing the same string. I would like to have a msgbox pop up, let them enter the directory name, and have that change all the odbc conneciton strings automatically. Can anyone point me in the right direction?

The message box would be a nice touch, but is not necessary, I can change it in the VBA editor if needed.

Currently, they connect using a Visual FoxPro ODBC driver. We setup the connection when we made the queries. Now that they are in on the worksheets, I want to be able to edit them with VBA instead of using the script editor (which is very slow).

View Replies!   View Related
Copyright © 2005-08, All rights reserved