VBA How To Select Specific Characters From A String

Sep 5, 2009

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.

ADVERTISEMENT

Extract String Between Characters After Specific String

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

Search A String For Specific Characters

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

Substitution Of Specific Characters In Alphanumeric String

Jun 13, 2013

I have a large column of text with multiple entries similar to this: PC3L-10600R-9-11-A0. I need to replace the "11" with a 13. However I have other instances where the 11 appears (PC3-12800E-11-11-D0 or PC3L-12800R-11-11-A0).

I have found that I can use SUBSTITUTION

{=SUBSTITUTE(A50,H50,I50,1) and =SUBSTITUTE(A61,H61,I61,2)}

to handle the specific instances but I'd like to have a way to combine this type of function logically in one command.

View 9 Replies View Related

Delete Rows With Specific Characters In A Specific Column

Dec 10, 2007

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 5 Replies View Related

Extracting 1st 4 Characters From A String?

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

Selecting 5th And 6th Characters From Right End Of String?

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

Remove Characters From Right Of String

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

Replace Characters In A String

Feb 11, 2009

Replace characters in a string. I have this macro:

View 2 Replies View Related

Collect First 7 Characters Of A String?

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

How To Trim Last 3 Characters From End Of String

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

Count Characters In A String

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

Formatting Characters Within A String

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

Remove X First Characters In A String

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

Remove Characters From End Of String Only

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

Taking The Last 5 Characters Of A String

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

Remove Last X Characters From String

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

Get Unique Characters Only In String Or Cell

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

Remove Non-alphanumeric Characters From A String

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

Formula To List Characters In String

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

Find Position Of Various Characters In String

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

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

Extract Trailing String Of Characters From A URL

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

VBA Partial String - Put First 4 Characters Of One Cell Into Another

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

Switching Words Around In A String Of Characters?

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

In VBA Remove Alphabetic Characters In String?

Feb 15, 2014

A string contain a number like AB12345 or B7845 How could I remove the alphabetic characters and keep only the number.

View 2 Replies View Related

Adding Space To A String Of Characters

Feb 6, 2007

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 9 Replies View Related

Find The First 3 Characters Of A Text String

Nov 17, 2007

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 9 Replies View Related

Pull Out Text Between 2 Characters In A String

Apr 3, 2009

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 9 Replies View Related

Identify BOLDED Characters In A String

Jul 6, 2009

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 9 Replies View Related

Extracting Texted Characters From A String

Apr 28, 2006

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 4 Replies View Related

Removing Characters From A String Variable

Jun 12, 2006

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 3 Replies View Related

Remove Non-alphanumeric Characters From A String

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

Inserting Characters Into A String Of A Certain Length

Jun 6, 2008

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 3 Replies View Related

Select Specific Cell Then Select The Row

Aug 13, 2007

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 9 Replies View Related

Extracting Data Between 2 Characters In A Text String

Dec 14, 2013

I have the data string below:

Career:25: 1-0-2 $13,765

I would like to extract the 1 between the : and - and as a seperate extraction would like te 2 between the - and the $ I have tried a few things but end up with the - as the length of the data changes

View 5 Replies View Related

Returning A Number Of Characters From A Variable String

Jun 4, 2014

If for example, I have a set of IDs like:

Bellen-234-B25R
Carlota-345-C20RDF
Quad-765-Q20LP

How would I return just the first 4 characters after the second dash? The closest I can get is ALL the characters after the second dash.

View 4 Replies View Related

Extract The First 5 Characters In A String, And Convert Them To A Number

Nov 7, 2008

Say I have a string "09800EBHR052708"

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

View 14 Replies View Related

Formula To Find Characters After Last Space In A String

Aug 27, 2007

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 9 Replies View Related

Excel 2007 :: Mask Certain Characters In A String

Jul 11, 2013

Is there a formula that I can put in AI that will mask the text character from text position 3 through the 2nd to last text position of each business name in AH? Where in the heck do you find XLGenie in the Excel 2007 ribbon? I have it installed and its in my addins list, yet nowhere to be found in the ribbon!

AH

13
ABC Rental

14
Professional Tool Service

15
Wonder Plumbing

16
Jim's Bar & Grill

17
Bobs Hair Salon

View 2 Replies View Related

Formula To Find Characters After The Last Space In A String

Aug 28, 2007

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 9 Replies View Related

ADVERTISEMENT