VBA - Extract Part Of String According To Certain Position / Markers?

Apr 10, 2014

Now do I extract a part of the string according to lets say "markers"?

Here is what I mean:

I use the code below to get the bottom range of my tables:

Code:
blimit = Activecell.Address
I haven't had problems because my tables were small, then I would:

Code:
blimit = Right(blimit, 3)
If the table was small there was no problem, since lets say the Activecell.Address = "$C$123", resulting in blimit = 123.

My how do I extract a part of the string, knowing I have 2 "$" ? That way I would make a version of this that always works.

I cannot use FIND or any formulas I am aware of since they all start counting from Right to Left... I thought of using Replace() to get the first "$" and change it for something else like "-" then looking for the second "$". But Replace() basically changes both signs... making my idea... well obsolete..

View 2 Replies


ADVERTISEMENT

VBA - Extract Part Of A String According To Position / Markers

Sep 11, 2012

I have been confronted with an issue that has bugged me: Now do I extract a part of the string according to lets say "markers"?

Here is what I mean:

I use the code below to get the bottom range of my tables:

Code:

blimit = Activecell.Address

I haven't had problems because my tables were small, then I would:

Code:

blimit = Right(blimit, 3)

If the table was small there was no problem, since lets say the Activecell.Address = "$C$123", resulting in blimit = 123.

My how do I extract a part of the string, knowing I have 2 "$" ? That way I would make a version of this that always works.

I cannot use FIND or any formulas I am aware of since they all start counting from Right to Left... I thought of using Replace() to get the first "$" and change it for something else like "-" then looking for the second "$". But Replace() basically changes both signs... making my idea... well obsolete...

View 2 Replies View Related

Extract Numeric Part From The String End

Sep 27, 2006

Extract Numeric Part From The String End with a macro, that J8 will turn 8, and R4 will turn 4.

View 9 Replies View Related

Extract Part Of Text String

Aug 10, 2007

CREATE TABLES LIKE BELOW?Column 1 Column 2
8 6CSS130 + 2CSS200g x10hanger 200 GM
250ml B2G1 Towel in bagx24 0607 250 ML
18 g-Mint 18 GM
100g 100 GM
150GM 150 GM

i have a data as above, i wanna to extract the 250ml from the column 1, only 250ml (basically i want to extract the size code like 150g, 100gm from the data above).

View 9 Replies View Related

Extract And Copy Part Of Text String From One Cell To Another?

Aug 22, 2014

I have to sort list of thousands name (3000 names) and remove duplicates. My problem is majority of the names have their title (i.e) Prof. Dr. Ir. Sir. etc typed in, so I need to copy the title in different cell and have their name only. Here's what I expect:

Current List ----> Column A -->Column B
Drs. H. A. Andrew Boston, MRE. ----> Drs. H. --> Andrew Boston, MRE
Drs. H. Andrew Smith ----> Drs. H. --> Andrew Smith
H. Abd. Mohammed Junus ----> H. --> Abd. Mohammed Junus
Prof. DR. Jane Doe, MD ----> Prof. D --> Jane Doe, MD
Prof. DR. Ir. H. Randy Wong, MBA --->Prof. DR. Ir. H. -> Randy Wong, MBA
Dra. H. A. Karen Patel ----> Dra. H. --> Karen Patel
Drs. H. A. M. Kangkong ----> Drs. H. A. --> M. Kangkong
Prof. Drs. H. A. Kareem Saleh, Ph.D -> Prof. Drs. H. A. ->Kareem Saleh, Ph.D
Dra. Hj. Nina Schorder ----> Dra. Hj. --> Nina Schorder
Ir. Abdul Jabbar ----> Ir. --> Abdul Jabbar

View 1 Replies View Related

Remove First Part Of String Of Text And Keep The Second Part?

Feb 12, 2014

Is there any way to remove the first part of a string of text in a cell and save the second part?

The first part of the text string is a team code that has a variable number of numbers, capital letters and sometimes spaces. The second part of the text string is a variable number of words in a team name that all start with a capital letter and have lower case letters. Every line has a different team code and team name.

The original spreadsheet also has a column with just team code. Is there a way of using this column to "subtract" the team code from the text string to just leave the team name?

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

Searching Through A String For Position Of 1st Non-Zero Digit

Sep 1, 2009

I have a text string in a cell, something like:

000024AC1011.

I need to find a way to find the first nonzero character in the string, and what position it is in. For instance, in the above example, 2 is the first non-zero character, so the function would return 5, since the 2 is the fifth character into the string.

View 2 Replies View Related

How To Find Nth Position Of Character In A String

Jun 12, 2014

I am trying to work out how to get nth position of a character in a string. For example in this

Code:

kjishdfiuayigdscka
if I use the formula
Code:
=FIND("i",I$1),

it will give me answer 3. But I want the position of 3rd "i" in the string. The answer should be 12. How can I change this formula to get the correct result?

View 9 Replies View Related

Last Numeric Character Position Of A String

Jan 29, 2009

In cell A1 contains a string, how do I get the number of position of the last numeric character from right to left (this value will be in cell B1).

Example: 12543AR3372C31WWW (In Cell A1)

4 (In Cell B1)

View 9 Replies View Related

Position Of A String In A Range Of Cells

Apr 24, 2006

why this code only works till the first assignment?:

Dim pos As String

Sheets("Sheet1").Select

pos = Range("A1"). Find("John").Address
pos = Range("A1").Find("Mary").Address
pos = Range("A1").Find("Peter").Address

In the second assigment

pos = Range("A1").Find("Mary").Address

I get a "Object Variable or with block variable not set" error message.

View 3 Replies View Related

Position Of Last Occurrence Of Character In String

Jun 29, 2008

on the web I've found this formula, which returns the postion of the last occurrence of the character in cell B1 in the string in cell A1:

=MAX((MID(A1,ROW(INDIRECT("A1:A"&LEN(A1))),1)=B1)*ROW(INDIRECT("A1:A"&LEN(A1))))

How do I turn this into a VBA function?

View 4 Replies View Related

Finding Nth Position Of Character Within Text String

Jun 16, 2012

Title should read: Finding the nth Occurrence of a character within a text string

I have a very long text string that is delimited by about 50 "/" to segment certain values within the text string. I want to be able to extract the text between the 33rd and 34th occurrence of "/". How to do this?

View 5 Replies View Related

Position Flashing Cursor At The End Of String In Textbox

Oct 15, 2013

I have a choice of 9 command buttons on a form that enter a short string of text into a textbox. Each button enters a different string into the textbox. The string is the prefix to a product serial number. Once the user has clicked the button & entered his preferred prefix I would like the cursor to be flashing after the last character ready for the user to manually type in the remainder of the product serial number. I'm guessing (with my limited ability) that I have to create a function to do this & then call the function when the button is clicked ?

View 1 Replies View Related

How To Extract Part Of Formula

Feb 2, 2010

I need to extract the part of formula in a cell. How can I do that?

For instance; I have formula: =C2/3579 in cell E2

I want to copy the last part in that formula i.e. 3579 to some other cell.

The last part (3579) is not constant in every report. It may vary in each report thus the length may vary from 4 digits to 5 digits also.

View 9 Replies View Related

Extract Part Of Any Workbook Into Another

Apr 10, 2007

I need to have a macro written which will allow me to extract information from any given workbook and copy them to my workbook. I'm thinking along the line of creating an input box upon opening the workbook, asking for the name of the workbook which contains the data. I would then use the data from the workbook and extract bits of information which I would copy to my current workbook.

However, I can't seem to figure out how to direct the answer of the input box to extract the information out of the target. And how would I refer to the workbook with the data in my code? I would need to refer to the data workbook as I need to extract information from it.

View 5 Replies View Related

Extract Last Part Of Workbook Name

Jun 10, 2008

Is there a way to extract a specific part of the workbook and save it as a value in VBA:

For example:

Workbook title is "Data Set 2007" How would you extract 2007?

View 5 Replies View Related

String Extract - Separate First Two Or Three Letters In A String

Sep 4, 2012

I am working with flight numbers and want to split out the letters from the digits. Examples,

BA1234A
BA123
EZY4566H
BE7893B

In column A I need the first two or three letters only,

BA
BA
EZY
BE

In column B I need everything to the right of what appears in column A

1234A
123
4566H
893B
etc.

View 7 Replies View Related

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

Extract Alpha From A String And Compare With Another String

Aug 23, 2007

I have a problems here. The problems is attached in the file. I wanna extract alpha/char from a string. Example: I wanna extract the words "(M)" with the bracket from the string "Toothbrush (M)" in column A. After extracting the (M) out, I wanna do a validation to compare the (M) in column B with another data in column C, if the (M) is same as the data called "Medium" in column c, the validation will return "Match" in the column d!

View 9 Replies View Related

Extract Web Data From Part Of Page

Dec 31, 2008

I would like to extract some data from a web page, but only part of it. The website is:

[url]

and I have a spreadsheet with many different ProjectId hyperlinks in a column (the final six-digit number). All I want is the large block of text that is in the middle of the page. Sometimes there is no text, as in:

[url]

So I don't know how to deal with that either...

I would just like to paste the text into a cell adjacent to the hyperlink. I know that there is a visibility issue with 1024 characters, but can cope with that (no text will be longer than the 32k+ chars).

Any help would be appreciated - it's for my wife's PhD, and I just want to make it easier on her. I am reasonably ok with macros, but am by no means expert! If you need clarification,

View 9 Replies View Related

Match Part Number Then Extract Quantity Onto A Different Sheet

Apr 3, 2014

I have 2 sheets in a file. One is given to me from our parts department containing all parts on a given truck model. The 2nd sheet is exported as a database file from inventor and imported as an update able sheet in excel.

I am needed to compare part numbers between the 2 sheets and the qty of each. So far I have it where it matches the part numbers and turns the ones I don't have in the model red. Now I'm trying to match the part numbers and pull the qty of said part from sheet 2, the imported one, and compare it to the to the qty in the 1st. All I really need to accomplish is matching the part numbers then copying the qty from sheet 2 to sheet 1 onto a new cell of the matched part number.

View 3 Replies View Related

Pull Out Part Of A String?

Oct 18, 2013

formula that will pull out the number preceding the * 60 in the text below? In this particular case I would be looking to get 5 as the result. That number could be any number but in the example below it happens to be a 5.

Channel Tune Duration in Seconds} (ID) >= (5 * 60))

View 4 Replies View Related

VLOOKUP And SUM On Part Of String?

Apr 13, 2012

My sheet contains values like these:

A/5
A/3
B/4

What I want to do is sum all the values after the / for a particalur value before the /:

A 8
B 4

I already tried VLOOKUP, but this function wants you to point to a certain cell to return instead of a just a part of the cell. It should return 5 for the first row to be able to perform a sum for every value of A.

How can I perform such a calculation without having to copy the values to separate cells? Can I contain all of this in one function?

View 5 Replies View Related

VLOOKUP For Part Of A String

Oct 4, 2012

Im sure this cant be done because it defies the logic of the LOOKUP function but I know there are some clever people on here so i thought id ask!!

I have 2 workbooks containing similar data from 2 different sources; the one common data item in both is a clients address but have the age old problem of data quality, which needs sorting eventually but i need a quick fix!

In workbook one for example the address will be recorded as RUSKIN RD

In workbook two the address will be recorded as RUSKIN ROAD

A simple look up wont work obviously as the 2 values dont match, then i thought of using the LEFT function but again this goes against the logic of the VLOOKUP.

View 2 Replies View Related

Remove Part Of String

Oct 29, 2009

I have a list of data that populates B2:B2900
This data is often prefixed by a 'reference code' that I wish to be removed.
Now rater than perform this manually aprox 3000 times is there a formula or some VB code that will complete this for me....

eg

The list of data is shown like:

SC7547-05 - Payne, Freda
SC8706-08 - Rungren, Todd
SC8714-05 - Travis, Randy
SC7517-03 - Beach Boys, The
Love Song
Now You're Gone
SC7512-01 - Horton, Johnny
SC8721-15 - Journey

So I wish for the SC7547-05 - to be removed from the first example to just leave Payne, Freda and continue this throughout the list
However if the data is found not to include this code (as in the 5th/6th examples above) leave it alone

So if the code of formula is run for the above the outcome would be....

Payne, Freda
Rungren, Todd
Travis, Randy
Beach Boys, The
Love Song
Now You're Gone
Horton, Johnny
Journey

View 9 Replies View Related

Vlookup Part Of String

Sep 29, 2006

I'm trying to use this formula to lookup the value in the array that matches, however, the first 4 numbers aren't known (thus why I'm doing the lookup) =VLOOKUP("*"&C2, 'KLIF Data'!A:C,1,0)

The * is being interpreted as a character, is there a way to perform the Vlookup function using a wildcard? Or just ignoring the first 4 characters of the cells in the array would work.

View 7 Replies View Related

Return Part Of A String

Mar 15, 2007


TX_jhfft_DATA.05-32_65 TGIF

I need to take out the number in the middle, from the period to the space. The number varies in length so just using the mid function doesn't work. Does anyone know how to modify the mid function to return all chars between the period and the space or is there some other function that may work?

View 9 Replies View Related

Vlookup To Search For Part Of A String?

Nov 17, 2008

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

Changing Colours Of Part Of String

Feb 22, 2013

I have a conditional formula which gives a value based on what the User specifies.

Example of formula is:

=IF($B$1=0,"CONTACT PERSON - OPTIONAL",IF($B$1=5,"CONTACT PERSON (FINANCE ONLY) - MANDATORY",""))

If the result of the string is " CONTACT PERSON (FINANCE ONLY) - MANDATORY " then
- word MANDATORY (not the whole string) should be in RED
- words (FINANCE ONLY) should be in BLUE
- remaining string should be left in BLANK

Not intending to use VBA. Is this possible?

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved