Regex Evaluate Part Of A String
May 19, 2014
I am currently working on creating a dialogue which will allow the user to input a range of worksheets to use for a pivot table.
I want the user to be able to input a variable into the dialogue. For example, I want the input "n-5 - n-1" to mean the 6th from last to the 2nd from last sheets. I am trying to find n - ([0-9]+)? and replace it with Worksheets.Count - $1. In other words, if there are 10 sheets, I want to transform "n-5 - n-1" into "5 - 9".
Here is the code I have so far:
[Code] ....
This has been working for strings like "n" or "n-4" but not "4 - n-3" or "n-5 - n-1".
Here is the code I use afterwards to interpret the string once the n's have been replaced:
[Code] ....
I'm new to Regex and VBA in general.
View 6 Replies
ADVERTISEMENT
Sep 24, 2006
I m trying to evaluate part of a string to determine if a number and if 6 char's in length. If Yes, return the 6 digit number, else 0
My formula below returns a #VALUE! error
It worked before I added the 6 char length condition, but sometimes return values of greater length that I do not need.
=IF(AND(ISNUMBER(VALUE(LEFT(C1809, FIND(" ",C1809)-1))),LEN(VALUE(LEFT(C1809,FIND(" ",C1809)-1)))=6),VALUE(LEFT(C1809,FIND(" ",C1809)-1)),0)
View 5 Replies
View Related
Jun 18, 2014
I have a string and a pattern. If a substring of the string is found in the pattern, the string is no longer valid. I would like to return a message stating which substring was found in the pattern.
String: K22+L09+P88+Q12+J56
Pattern: (N75|X02|A21|U67|L09|...)
Output Should Be: "J56 is an invalid code. Blah-Blah"
Code to test string against pattern:
Code:
Function Test_OPC(sCode, sPattern) As Boolean
Dim RegX As Object
Dim RegMC
[Code]....
View 2 Replies
View Related
Feb 11, 2010
How can I evaluate just the first part of these stirings so I can just do something like Left("Ca",2)?
View 2 Replies
View Related
Feb 19, 2012
I have the following in VBA:
Code:
ActiveCell.FormulaR1C1 = _
"=mod_a1&"" ""&mod_b1&"" ""&mod_c1&"" ""&mod_d1&"" ""&mod_e1&"" ""&mod_f1&"" ""&mod_f1&"" ""&mod_g1&"" ""&mod_h1&"" ""&TEXT(mod_i1,""m/dd/yyyy"")&"" ""&mod_j1&"" ""&TEXT(mod_k1,""h:mm:ss"")"
I only want the user to see the string, not the concatenation formula. So how do I get VBA to turn this formula into an actual string.
I have seen some functions using ByVal and Evaluate with this sort of thing but this is just different enough that I can't figure it out.
View 4 Replies
View Related
Apr 3, 2008
I want to be able to use a macro to read cells from a certain column so that it will evaluate a text string untill it hits a blank space, then copy the text that it found. I'd like it to work on Excel 2003 or later
View 9 Replies
View Related
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
Dec 10, 2013
I've been trying to get this SumProduct formula to return anything except an error for a while now.
=SUMPRODUCT(('Main Log'!$E$13:$E$17>=$C$2)*('Main Log'!$I$13:$I$17
View 2 Replies
View Related
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
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
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
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
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
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
Sep 27, 2011
I have a find and replace function that removes + smybols from a coloum of strings. How can I remove the first instance of a space (if it later contains a +, too?
E.g.
Before: [ +Test +Test]
After: [+Test +Test]
Code:
Columns("D:D").Select
Selection.Replace What:="+", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
View 4 Replies
View Related
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
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
Aug 15, 2014
I am trying to delete part of a text string for a range of cells. The code I have so far deletes the first set of letters but is looking in every cell in the range.
Ideally I would like it to only look in the cells of col B where there is data then only delete the first 10 letters if the cell begins with "Service ID"
Sub DeleteServiceID()
Dim c As Range
For Each c In Range("B1:B1000")
c.Value = Right(c.Value, 10)
Next c
End Sub
View 4 Replies
View Related
Feb 25, 2009
Find part of string and delete. I have the following type of string:
View 4 Replies
View Related
Aug 23, 2012
How do I copy the numeric part(11 Digits) of a cell
EG. A1 = "GL CREDIT DDA07 60291627599 ZFT"
I Require "60291627599" in A2
Descr
ANSWER
GL CREDIT 62180577391@DDA ZFT
62180577391
GL CREDIT 62300192854 62300192854 DDA06 ZFT
62300192854
GL CREDIT ACC REC 62273991367 ZFT
62273991367
The numeric 11 digit number is not always in the same position in the cell.
View 2 Replies
View Related
Aug 23, 2012
How do I copy the numeric part (11 Digits) of a cell ,
E.G. A1 = "GL CREDIT DDA07 60291627599 ZFT" in only want "60291627599" in A2
Descr
ANSWER
GL CREDIT 62180577391@DDA ZFT
62180577391
GL CREDIT 62300192854 62300192854 DDA06 ZFT
62300192854
GL CREDIT ACC REC 62273991367 ZFT
62273991367
11 digit not always in same position in cell.
View 7 Replies
View Related
Jan 3, 2013
I have a string that with spaces in between.
example:
X3PP2 1200 U
I would like to extract the "1200". How can i do this in VBA?
View 4 Replies
View Related
Feb 11, 2013
I belong to a fantasy cycling league and after every race, I copy the race results to award each rider their points for the day. The site I visit most frequently lists the riders results in this format:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00
2. Lance Armstrong (USA) Team Livestrong etc... 4:06:00
I usually copy the results to my spreadsheet then calculate each riders points.
The'results end up looking like this in Excel:
1. Alberto Contador (Spa) Team Sax Bank Tinkoff 4:05:00
2. Lance Armstrong (USA) Team Livestrong 4:06:00
So, the data ends up in 3 cells. The position in one cell, the rider and their nationality and their team in another, and the time of arrival in another cell
Is there a way in Excel where I can get rid of or delete all the data after a rider's name?
I usually end up pasting the rider information to Word, then replacing the (***) with a tab symbol, then pasting back to Excel and get rid of the data that's after the name by deleting the cells to which that data was transferred.
Is there an easier way to do this in Excel, or a macro I can create?...
View 9 Replies
View Related
Nov 17, 2006
Is it possible to lookup if at least 8 characters of a text string is found in another text string?
View 9 Replies
View Related
Nov 17, 2006
Is there any way I can Vlookup for just part of a text stiring in a cell.
I am trying to vlook if a part number contains the letters “HFR” if it does I want
It to refer to the comment I have made in the second column of my vlook table against HFR.
View 9 Replies
View Related
Dec 3, 2008
I have a worksheet that I need to filter on 8 columns. I need to do this in the form (Col A = Bob OR SAM) AND (Col B = Apple OR Pear OR Banana) AND... etc for 8 columns.
I started this thread and we reached the conclusion that I needed either a macro to list out all the possible combinations on separate lines, or a complex criteria formula.
I'm trying to put together the complex criteria, but I am further stumped by the fact that some of the columns contain multiple terms with comma delimiters, so I'm trying to match part of a string, rather than a whole string, so the usual = ISNUMBER(MATCH(Sheet1!A2,Sheet2!$A:$A,0)) won't work. I tried using =ISNUMBER(FIND(Sheet2!$A:$A,Sheet1!A2)) instead, but I think this fails because it should be an array formula. I tried normal entering and CSE entering, and neither work, so I think this is a dead end.
View 9 Replies
View Related
Dec 10, 2009
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 9 Replies
View Related
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
Sep 29, 2006
I have a Userform (PictureViewer) with a.o. a combobox and a textbox on it. The combobox has a picturename with the full path, i.e. C:My DocumentsJohnMy PicsSummer VacationImg_0001.jpg. The textbox will have the replacement name for that picture entered in it while the picture is visible, i.e. Harry and Antoinette (no extension). Pictures get cycled on this Userform with a forward and backward button. When I click the forward button, the next picture opens up and the name of that picture replaces the previous name in the combobox. What I would like to do is on the click of a commandbutton, copy the string (full path) in the combobox to a column (A), starting at the first cell and in the column beside it (B) end up with the new picture name including the full path. In this case, that would be C:My DocumentsJohnMy PicsSummer VacationHarry and Antoinette.jpg. The next picture name I want to change would be in the cells below this. This could go on for many pictures. I have the macro to change the file names once when I have cycled through everything I want to change. Is it possible to do this in one click or do I have to split the drive name, all folder names and picture name, replace the picture name with the new name manually and concatenate it again? The depth of folders can vary of course. The macro for renaming is as follows
Sub RenameTheFiles()
Dim OldName As String
Dim NewName As String
Dim LastRow As Long
Dim I As Long
LastRow = Range("A65536").End(xlUp).Row
For I = 1 To LastRow
OldName = Range("A" & I).Value
NewName = Range("B" & I).Value
Name OldName As NewName
Next I
End Sub
View 9 Replies
View Related
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