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


Advertisements:










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 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
Use VBA And Select A Specific Print Area
is there a way to use VBA and select a specific print area.
i want only up to column 'z' and to select from row 'a' to the final row with data in it.

View Replies!   View Related
Delete Rows With Specific Characters In A Specific Column
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):

Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete

although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:

Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next

but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...

View Replies!   View Related
Select Specific Cell Then Select The Row
I have column A with various values in cells.

For instance, DG, GS, HG etc

I wanna do if a cell in column A is equal to DG then select the entire row that contains the cell. then call other sub.

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

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
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
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 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
Averages - Specific Characters
I need a formula that determines the average of numbers based on specific cells.
For instance:

COLUMN A COLUMN B
John Doe 1.48
Jane X 2.89
John Doe 1.89
Jane X 5.98
Michelle Doe 5
Claire Doe 3
Jane X 0

What formula can I use to give me the average of "Jane X" not taking into consideration the one with the 0 Value?

View Replies!   View Related
Replacing Specific Characters
i got a a sheet with over 10000 entries, problem is that it has been compiled by many people, each which has his or her own way of entering the data. Here is the problem i have a field in which they can enter a coordinate X,Y and a corresponding area. the sytaks is Area XXYY so it should look like this P20522. Where P2 is the Area and X=05 and Y=22.

most of the entries has the area in a different field and is written X5,Y22 but some is done correct some without the comma, further more i need to add the 0 in fron if it is a number below 10.

View Replies!   View Related
Replace Values Between Specific Characters
I have been trying to do the following but have not been successful. thanks in advance for helping!

- i have lots of text strings on one worksheet

- some text strings have <randomstring>, some even have multiple instances of <randomstring>, but others don't have a <randomstring> at all

- what i want is remove anything within "<" and ">", including the operators "<" and ">"

i have done this so far, with simple text functions, have each of the following lines in separate columns (text string column is "C"): .....

View Replies!   View Related
Banning Specific Characters In A Cell []
I need to ensure that the client names entered don't contain certain banned characters (* / : ? [ ]) as the name entered will be used later to create a file name. This is what I have written (in the Worksheet_Change event):

If Target.Column = 1 Then
If Target.Value 0 And Target.Value "" Then
Dim intFound as Integer, ranFound as Range, i as Integer
Application.EnableEvents = False
Application.ScreenUpdating = False
intFound = 0
For i = 42 To 93
Set ranFound = Target.Find(Chr$(i), LookIn:=xlValues, lookat:=xlPart)
If Not ranFound Is Nothing Then
Select Case i..........................

Which is all very well, but it treats character 42 (*) and character 63 (?) as wildcards and even good client names are rejected. How do I search the entry to find if there is an actual * or ? in the target.value?

View Replies!   View Related
Formatting To Remove Specific Characters
I have several hundred columns of data in this format: |#########|. I need to remove the non-numeric characters on the edges of the numbers so I can manipulate them. Is there an easy way to do this? I do not want to manually remove all of the characters.

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
Extract X Number Of Characters Followed By Specific Character
How can I extract mid section of the string which is always 5 characters long and is always followed by a period "." ?

My account strings are in 3 sections (but the third section isn't always used)

For example,
1.10210
1.22556.001
900101.56201
955261.54444.001
5566625.58886.957756

View Replies!   View Related
Delete Characters After Last Specific Character Occurence
I have one column with many numbers. Some have one dash and some have two.

Example:
123-123456-65
012-789546-1
98B12354-889

Is there a way that I can remove all characters after the last – (dash) in the number?
Example:

If number is 123-123456-65
Then 123-123456

If number is 98B12354-889
Then 98B12354

If someone could just lead me in a direction, I might be able to figure it out. However, my code is elementary and most of the time, I record macros and the play with the code until it does what I want.

View Replies!   View Related
Change Characters In Specific Position Of A Text File
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 Replies!   View Related
Determine Sheet Names Starting With X Specific Characters
I have a worksheet that will have tabs added as time goes by and a macro within the workbook currently selects all of the sheets and copies and pastes formulas within all of the sheets simultaneously. Right now, if a tab is added, the macro needs to be updated to select that specific tab name as well.

I would like to simply have the macro select all of the sheets starting with "IL" until the sheet before "reference", or all sheets except the first 3 and the last 2 if that is easier. Any ideas? And thank you for your help, you people really amaze me sometimes!

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
Delete Rows Where Columns Have Specific Lower/Upper Case Characters
I am trying to detete rows that have several specific values, upper and lower case, (A-H) in any of several columns (B through H). I can delete upper and lower case "D"s in column B, but I'm having difficulty stringing together several variations and getting at the "D"s that are midstream (such as: ADeC), I tried ("*D*") to no avail.

With . Cells(Lrow, "B")
If Not IsError(.Value) Then
'****
If LCase(.Value) = LCase("D") Then .EntireRow.Delete

View Replies!   View Related
Testing A String In A Select Case
Excel 2007 on XP, must be compatable with '03

I have a macro that sorts sets of data that is imported from a LIMS database (SQL). The macro then sorts the data based on a characteristic of the dataset that was pulled in.

I did have the macro working, however now I must add in some AI that groups together certain datasets that don't share the same parameter, as well as discard incomplete datasets, and group together unknown datasets.

In the code following the trouble section, I isolate the required tag, placing it in labelDecoded(2). Now I must test this variable against a couple of cases to see if it is one of the exceptions to the normal rule (which is to Place it in the worksheet named after what is in labelDecoded(2). A switch statement is nessasary as this is still in a prototype/proof of concept phase, and the sorting AI will be expanded in the finished product (As in more cases will be added).

Select Case labelDecoded(2)
Case Mid(labelDecoded(2), 1, 1) = "G"
getSheetName = Gas
Case "DPLS" Or Mid(labelDecoded(2), 1, 2) = "UD"
getSheetName = "Other"
Case Else
getSheetName = labelDecoded(2)
End Select

I have highlighted the sections that is giving me an error in red. The first section "Mid(labelDecoded(2), 1, 1) = "G"" Fails to evaluate to true. The Second section "Mid(labelDecoded(2), 1, 2) = "UD"" Throws a type mismatch error, that causes the macro to execute some error handling code, which by co-incidence, is the same as that case evaluating to true.

When a switch statement is called is there an object or variable that holds the information that is being processed by the switch statement? I recognize that my problem is most likely that I am calling the variable that I have allready tested to be tested again.

View Replies!   View Related
How To Find A STRING In A Worksheet And Select The Cell Containing It
I want to know how to find certain String and select the cell it on finding the String.

View Replies!   View Related
Delete Specific Word From String
I want to delete a specific words from string but i have a problem with the code below. For example, i wan to delete the word "Inc" only but the problem with my code is that it is deleting from "Incorporated" too and i want only the code to delete only if it finds the word "Inc" only.

View Replies!   View Related
Find Specific String In Cell
I have at database which i want to search in... The problem is that i wanted to search in specific cells, or ranges. So i made a for loop searching for words in one range.. But it doesn't work.

For i = 0 To antal - 1 Step 1
Worksheets("Søg").Select
If Range("B5") = "X" Then
Sheets("Database").Select
c = InStr(1, Range("B" & 2 + anRow * i), sgbygsag, 1)
Sheets("Søg").Select
Range("A1") = c
End If
Next

anRow is the delimiter between to databases. And sgbygsag is the string i am searching for, i have made sure that this really is a string. No matter what I do this code sets Range("A1") to "0".


View Replies!   View Related
Finding A Specific Integer In A String
I have a string in one of the following formats:

Banana 2, Orange 5, Lemon 0
Banana 7, orange/Lemon 9, cucumber 6
Melon/grape 3, Pineapple/ Orange 1
Banana 1, orangefruit/Lemon 2, pine 8

I would like to take out the first integer that comes after the word Orange (not case sensitive). I'm kinda at a loss here, how do I go about accomplishing this?

View Replies!   View Related
Sorting Rows On Whether They Contain A Specific String
Lets say I have a 2 column sheet (A and B). And I want to sort on column B with all the rows that contain a given string, i.e. /g or Äåßôå.

Something like doing a find all but then being able to select/copy/cut etc those rows.

How can this be done?

View Replies!   View Related
Parsing A Specific Part Of A String
I have code that retrieves the body of an email. I need to parse out certain parts of the text. For example, the text will look like the following;

LastName: John Doe
Email: johndoe@aol.com
Cell#: 555-555-5555
FileRequested: xxxxx.xlsx

I have the code to find where the specific item, ie LastName, starts in the whole text. I need to retrieve everything to the right of the : before the CRLF. That's what Im having trouble with.

View Replies!   View Related
Find A Specific String In A Cell
I have absolutely no idea how to get starting on this one. I've got a long string in cell B1. At some point there is the word "oms:SomeString," (without the quotes). I need to know whether SomeString is somewhere in the active sheet or not (the workbook running the VBA-code is not the active sheet). I can't just compare the cell B1 because it contains multiple words. Any hints are very welcome.

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