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


Advertisements:










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

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

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 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
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
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
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
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
Search Through Cells With Alphabet Characters
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 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"
Else
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
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):

C-2C-9C-9-XA-10A-10-BA-10-XA-8N-7-D

The function would strip the "-" to leave:

C2
C9
C9X
A10
A10B
A10X
A8
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
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
VBA - Search For A String
Scenario:
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 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
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
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

Operation:
- 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
Search A String For A List Of Matches
I am looking for a way to check for the presence of a member of a list in a field. Not for an exact match, but for the text to be somewhere present. I know I can use the "search" function to find text within text, but I don't see how to find the presence of any member of a range of values (a list) within the text.


View Replies!   View Related
Search A Cell For A String Of Numbers
I have the below data to look at every day. The Data codes column A contains a part number and the Info Column B, the information about the part. I have in column C the serial numbers that change daily, I copy and paste this column in everyday.What I would like to do is search within the serial numbers for the data codes and return in the Result column D the info relating to the data code.

Data codesInfoSerial numberResult118Type A118F2041Type B118F BNG59617Type C2041F2151Type D2066F=9617570Type E2151F800Type FEXPEDITE COPY:570F=MEDIACOM PDF687Type GEXPEDITE:118F

Currently I spend hours removing all of the text and symbols (there are normally 2500 to 3000 serial numbers) and then do a simple vlookup. Is there a formula that could search the serial number to find the data code? Or some code I could use in a macro to do the job for me?

The serial numbers change daily but the data codes and info in the first 2 columns remain contstant - there are nearly 250 data codes.

View Replies!   View Related
Search Comma Delimited String
I have a spreadsheet with a number of columns containing comma delimited strings (years) which I need to check cell by cell and return false if anything apart from the years 2001 to 2008 is found.

For instance, a cell may contain the years (2001, 2005, 2006, 2007, 2008) or (2006, 2007) or (2001, 2004, 2008) or (2004) or any combination of those 8 years.

I thought of using the Split function on each cell and then looping through the resultant array to do a comparison against each of the 8 years but with a large number of delimited strings to check it could be a bit time consuming. Any idea how I could accomplish this more quickly and efficiently either with a formula or VBA?


View Replies!   View Related
How To Search For A String That Includes The Symbol
I'm writing some VB in an Excel macro to search and replace a string of text.

The problem is that the string contains the " symbol, so when Access gets to that symbol it thinks that it's reached the end of the string.

e.g.

I want to replace "0519","863027" (including the quotes) with "0001","863027"

This is what the code looks like, but it obviously doesn't work :

View Replies!   View Related
Formula / Macro To Search For A Particular String
how to use a formula or macro or whatever that is applicable to solve my current issue.

For example:

I've the following text below. The prefix of "atest", "bgo", "crun" and "dfly_c" are fixed pattern, however, the text after prefix will change accordingly.

atest_myhomeisfar
bgo_runfast
crun_b:veryfast
dfly_c:bluesky

Now my request is, I would like to have the final output as per below. Notices that there are some additional numbers behind the text (with semicolumn).

Final output:
atest_myhomeisfar:1998
brun_veryfast:2009
cfly_bluesky:1790
dfly_c:bluesky:1800

My question is, is it possible to create something to handle this issue? I mean something that will seach thru a particular fixed pattern as mentioned above and add the number behind it?

I would like to do it one at the time (one prefix at a time). Not mass searching and adding.

For example:
Search all "atest*" pattern and add an additional number behind.

View Replies!   View Related
FindNext (search In Column For A String)
1) search column A for a string
2) as that string is found, move the entire contents of that cell to the same row, column F
3) insert a blank row above the row where the text was found
4) continue to search and perform the same steps

I was able to come up with the code to find a single instance and perform the necessary steps, but I've tried 'til I'm blue in the face to incorporate the FindNext object to parse through the entire worksheet. I thought I was on the right track with the attached code (which could be completely wrong), but it returns my MsgBox "Value Could Not Be Found."

View Replies!   View Related
Search For Word In A String Of Data
I have put a formula in excel to count how many times the word 'administration' appears in a column:

=COUNTIF(K2:K99,"Administration")

Unfortunately, the output that I am searching has mulitple words in it, separated with a colon and no space. My formula skips the count if the word Administration is not completely on it's own

e.g. Administration counts 1
Administration;Cardiology does not count

View Replies!   View Related
Nested IF(SEARCH The String In Column)
Column A contains an alphanumeric string up to 50 characters in length. I need a formula to search the string in Column A for the word "Appeal" and place that word in Column B when found. Likewise for the words "Grievance" and "Complaint". If none of the three words are found in Column A, the contents of Column B should be blank. Here's the formula I've played around with (it works on the first line but returns #VALUE! on following lines):

=IF(SEARCH("Appeal",A1),"Appeal",IF(SEARCH("Grievance",A1),"Grievance",IF(SEARCH("Complaint",A1),"Co mplaint","")))

I've tried entering it as an array, but that also fails. This seems like a pretty simple problem ,but I've spent several hours trying to make the formula work so I'm obviously doing something wrong.

View Replies!   View Related
Vlookup To Search For Part Of A String?
If I have a store number (say 1234) and I am trying to search for something which contains 1234 (i.e. the whole string of ABCD1234, ABCD1235, ABCD1236 etc) is it possible to do this in one VLOOKUP formula?

One way of doing it would be to VLOOKUP("ABCD"&"1234",....) but I would rather do it the other way around, to prevent the formula from not working.

View Replies!   View Related
Search Each Cell In Row For Text String
I am using VB and need to search each cell in the row for a string "U30". If it appears, then I need to grab that plus the next three digits, "U30XXX". Otherwise, I need to grab the last six in that row (=RIGHT(M2,6)). I am not sure how to structure the If statement in VB. I searched the forum and help files, but was not sure what to do from here. Currently, I have...

Range("I2").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[4],6)"

Range("I2").Select
Selection.AutoFill Destination:=Range("I2", Cells(Rows.Count, "I")), Type:=xlFillDefault

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved