Parsing A String Variable
Dec 8, 2006
It's been a long time, but I use to parse a string in C.
How do you strip off the long directory path of a string to just the filename and extension?
String Example:
S:2006 jobs602f EID Bass Lake Recycle BPSConstructionSubmittal Review16940-Instrumentation602f-16940-01.rev.doc
Wanted result:
0602f-16940-01.rev.doc
View 9 Replies
ADVERTISEMENT
Mar 28, 2006
I am trying to split up a cell into numbers and charachters and place them in
separate columns, but the lenght of the number part varies as does the
content of the character part.
For example, one cell could be 5#, 10Tins, 4 lb, 100Pcs, etc.
I would like to be able to pull out the #, Tins, lb, and Pcs in to their
own column.
I have found this formula:
=LEFT(A1,FIND("-",A1,1)-1)
but it assumes some level of consistency, the "-" in the cell.
View 18 Replies
View Related
Nov 16, 2008
I am trying to figure out how to parse some strings. Example of strings
View 2 Replies
View Related
Jun 21, 2007
if i have to search for some special character in a string and i use Instr function then which of the following is the correct way. Lets say i have to search for "****" in the string
(1)
instr(myString,"****")
(2)
instr(myString,"****")
View 5 Replies
View Related
Apr 10, 2008
I have a series of codes, such as:
xxx-xxx-xx/xy-xxx
yy-yy-yy/yz
zzz-zz/za-zz/za-zzz
The /'s represent variations on the same code, so the first one is shorthand for:
xxx-xxx-xx-xxx
xxx-xxx-xy-xxx
I would like to be able to split the code into a new line (or lines) wherever a / occurs.
There are any number of dashes and slashes within the code, however, there are none at the beginning or end of the code.
The slashes will be always surronded by dashes eg xxx-xx/xy-xxx.
View 9 Replies
View Related
Jul 23, 2009
I have some code that will parse the FIRST IP address it finds in a string. Could someone help with the code for continuing through the remaining part of the string and parsing out any additional IP addresses it finds and concatenating them with a comma and space (", ")?
There are specific rules for validating a "proper" IP, but for the purposes of my work, I don't need to validate. I found the following code on a google group (thanks Jeff M). All it does is parse the numeric text surrounding the 3 periods that all valid IPv4 addresses contain. For my purposes, it works ....
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
Jun 10, 2014
Column M:
##/##/#### | Variable Length Text-####
Example:
01/06/2014 | Daniel Trimble-4048
I need to parse out the different parts of Column M.
In Column R -- "Close Date", I'm successfully using:
=LEFT(M2,FIND(" | ",M2)-1)
...to extract the close date of the donation.
In Column S, I want to list the donor name--which is all of the text after " | ", and before the "-".
I don't need anything after the hyphen, and fortunately in this data, no one's name has a hyphen in it.
The Close Date is working fine for the LEFT and FIND functions, but for the life of me, I can't seem to get MID to work for the variable-length text. The text will always start in the same position -- 14, as the date and delimiter are standardized. And the last 5 characters of the text are not variable in length, so they can be cut out completely.
How do I use MID to extract everything starting at position 14, and stopping 5 characters short of the end of the text?
View 12 Replies
View Related
Feb 14, 2010
I am reading lines from a textfile. Each line in the textfile has the identical format: textstring1:textstring2. The two strings are always separated by the : character. I have the code to get textstring1, but because I'm a rookie, I can't figure out how to get textstring2. See the code in bold, this is the line I need to get textstring2.
View 4 Replies
View Related
Feb 1, 2013
I have an Excel sheet with lots of shortcuts in the first column. It's the entire shortcut, including the .lnk extension.
What I need to do is to be able to parse out the string to make the shortcut human readable when I create the hyperlink. I'm saving the output as HTML for a user to open these shortcuts. It's on an internal LAN & secure. Using various versions of Excel (2010, 2007 & 2003, predominately the first). Trying to make an ugly, 140+ character shortcut into a more readable form for web navigation purposes.
Up to a point, the shortcuts are similar:
a123meworkfolder1folder2folder3shortcut1.lnk
a123meworkfolder1folder4folder5shortcut1.lnk
a123meworkfolder1folder6folder7shortcut1.lnk
a123meworkfolder1folder8folder9shortcut1.lnk
but, later on, they vary from that point out.
a123meworkfolder1folder10folder10Afolder11shortcut1.lnk
a123meworkfolder1folder_12folder12Afolder12Bshortcut1.lnk
The strings are the same up to the 7th occurrence of the "" character, but after that, the foldernames start to change & the shortcut may be down a level (an extra folder) or more. Also, the lengths of the strings change as well, so I can only do positional parsing up to a point.
View 4 Replies
View Related
Dec 7, 2008
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.
View 3 Replies
View Related
Nov 10, 2006
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View 5 Replies
View Related
Feb 18, 2014
I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.
View 2 Replies
View Related
Oct 30, 2008
I understand that a everything is already considered to be string, but how would I phrase this?
If txtBolow = (a statement which indicates the textbox is full) Then
txtBolow. SetFocus
txtBolow. SelStart = 0
txtBolow. selLenght = Len(txtBolow)
If txLowl = ("") Then
txtLowl. SetFocus
txtLowl. SelStart = 0
txtLowl. selLenght = Len(txtBolow)
What would I use to indicate that the user has completed the data in the textboxes, and the old data is highlighted to make way for the new?
View 9 Replies
View Related
Aug 13, 2009
I am trying to use a variable for the name in the Sheets command so that I can put in the names of the CSV file that I have captured in a variable. Here is the macro that I recorded using Excel 2007:
View 4 Replies
View Related
Dec 24, 2009
I believe I should use the INSTR function, but I simply dont understand its use very well. I have a string in a cell, "MEAS/SPHERE,F(QUA_1),5", the characters I need start at the "(" and end at the closing ")", ref: (QUA_1). The start and end will always be the same, the length of characters and the spelling of characters be anything. Thus, QUA_1 can be CAL_1, CAL_2, SOMEOTHERTEXT_1 or 187bgo_199, etc, always enclosed in "( )". I wish to place the characters and the closing "( )" into a variable as a string.
View 5 Replies
View Related
Jan 8, 2010
CALIB/SENS,S(S5a90b-90),FA(CAL_15)
This one has two sets of quotes, with the information from the noted post I can retrieve the string "S5a09b-90", now that I have a double set of brackets "( )" how do I get the value for the second set of brackets, "CAL_15"?
In the quoted string, CALIB/SENS,S, is constant. The first set of brackets are constant (string length is not), the second set of brackets are constant (string length is not).
View 7 Replies
View Related
Sep 16, 2009
if it is possible, to use a string variable in a Formula via VBA.
For example: ActiveCell.Formula = "=SUM(Sheet3!strVariable)"
Of course the code is not working that way. The Variable has the following structer: CellAdress;CellAdress;CellAdress... so it would perfectly fit into the formula-syntax.
Is there any possible way to do that? The reason behind it is, that I have to deal with a flexible range and I want to process this range with a formula. Actually the range is flexible, because of different filters and I only wanna count all numbers in the unhidden rows.
My varibale would then contain all celladresses, for the visible cells.
So in a nutshell: is it possible to somehow use a varible in a formula?
View 9 Replies
View Related
Oct 20, 2009
I have a string variable contacting a file.
I want to set a workbook variable to this file, WITHOUT actually opening it!
ie:
DIM wb as Workbook
DIM filenm as String
filenm = "C: empdata.xls"
set workbook = filenm
============================================
So I know this won't work, but is there anyway around it? (bearing in mind I don't want to open the file!
View 9 Replies
View Related
Apr 21, 2006
Just a quick question, is it possible to use a string variable in a Excel formula (in VBA)? Im trying to do the following in VBA
mortgageSchedName = acName & " Schedule"
Range("B6").FormulaR1C1 = "=mortgageSchedName!RC[254])"
the above results in an error because there is no such sheet as "mortgageSchedName", but I want it to access the value of that string variable.
View 6 Replies
View Related
Feb 9, 2014
i just wanna extract the date shown(last data until meet the first comma) in first column to another column.
column 1
abcde, 1234
abcd, 12345
abcdef, 123
column 2
1234
12345
123
View 3 Replies
View Related
Aug 2, 2014
I have several words in sheet2.one word per line. every line in sheet1 should be checked and deleted if the line (colum 3 and 4) contains any of the words in sheet2. i decided to go with two for-loops, my Problem is a error in line 7 and 9 (indicated by arrows). it seems to me that ...Cells(...).Value is not allowed for strings. i already tried .Text, checked several VBA Forums but could not find a solution.
View 13 Replies
View Related
Oct 24, 2011
I have the following code.
Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array("ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"), Operator:=xlFilterValues
The values ELDU238 - ELDU247 are values in a column beginning at A2. I want to create
"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247"
in a string variable strArray which will enumerate whatever values are in column A beginning at A2 and create the above. I can then have
Code:
ActiveSheet.Range("$A$1:$AF$" & lngRows).AutoFilter Field:=x, Criteria1:=Array(strArray), Operator:=xlFilterValues
Hence if the number of entries changes e.g. say I have
"ELDU238", "ELDU239", "ELDU240", "ELDU241", "ELDU242", "ELDU243", "ELDU244", "ELDU245", "ELDU246", "ELDU247", "ELDU248"
the code will still work.
View 4 Replies
View Related
Nov 28, 2012
I'm taking some university classes and one of them is called Applications Programming and we have been using excel VBA. I have a project that I'm working on, and it's very basic. Basically we need to create a userform that has 3 option buttons, 4 check boxes, a couple labels and a couple text boxes. It's suppose to be a ice cream selection terminal. Option boxes for basic flavors of ice cream, check boxes for toppings, one text box for user input of number of scoops of ice cream and the last text box to output a reciept/overview of purchase.
So here's the question... I was wondering if there's a way to change the font of a single string variable, or change the font of a word within a string. My output to the saleBox(My receipt overview) is this:
saleBox.Text = ("Thanks you for your Purchase") & vbCrLf & vbCrLf & _
"You ordered " & numOfScoop & " scoops of " & flavorChoice & "ice cream" & vbCrLf & _ "Topped with: " & toppings & vbCrLf & vbCrLf & total
(not sure how the code is gonna look in the message but I'm sure you get the idea)
i want to be able to change the font for the flavorChoice or toppings variables. They are both strings.
Anyways, I know I can get away with basically what I have there and get full marks, but I like going above what the basics are. If it's kinda complicated.
I'm gonna attach a screenshot, more info is better than not enough right... nevermind its askin for a url!
*** I just now got the idea of putting another textbox over the area that would output the text I want to change and hide the border or something and then change the font of that text box through the properties ***
View 3 Replies
View Related
Dec 18, 2012
I'm trying to check to see if a file exists using VBA but the routines I see use a string that has the path and then the file name. I have a fixed path C:VBtesting and the a variable file name. I'm using a file name that consists of a username & date such as cwilliams201212. I'm having problems getting the variable file name into the string.
View 1 Replies
View Related
Jan 16, 2013
I'm getting the generic 1004 error on the Range.FormulaR1C1 line, but I can't seem to see the problem.
Code:
Sub UpdateFormulas()
Dim stockFund As String
For i = 2 To finalRow
[Code]....
The mouse-over on the stockFund variable in that last line shows the correct cell address as the value and I checked the If statement to ensure it actually finds the number. I would guess that it would be a syntax error with that line, but it looks correct to me.
View 8 Replies
View Related
Feb 12, 2013
I have a comment that I'm putting into a variable for the purpose of obtaining the persons name.
Cell C3 (in this example) just contains their employee number, but it's comment contains...
Tech ID: 123456
Name: John Doe
The Tech ID is variable from 3 chars to 6 chars and obviously the name is size is also variable. The end result will be a variable with just the persons first and last name extracted from the comment.
Code:
Dim mycmt As String
Dim techname As String
mycmt = Sheets("tech").Cells(3, "C").Comment.Text
techname = Right(mycmt, BIG-OLE-BALL-OF-CONFUSION)
MsgBox techname
My 2nd question. This is the 3rd time in recent days that I've needed a partial string and Mid, Left, Right, Len, Find just boggles my mind.
View 6 Replies
View Related
Nov 1, 2013
I'm trying to use a variable that hold a string in Shell string command. This shell is the command to take a snap picture over a given region and store the picture to a pre determined file name
***The code do compile***
The string variable is : StringAddress = "g: estsnapasnap1.jpg"
The following shell command will work
RetVal = Shell(("C:Program Files (x86)HyperSnap 7HprSnap7.exe " & " -hidden
-snap:x3602:y155:w1432:h830 -save:jpg g: estsnapasnap1.jpg"))
This one, with the string address variable, wont work
RetVal = Shell("C:Program Files (x86)HyperSnap 7HprSnap7.exe " & " -hidden
-snap:x3602:y155:w1432:h830 -save:jpg " & StringAddress)
View 1 Replies
View Related
Jan 8, 2014
I'm having trouble setting the Range "UtilizationRange" using a variable, "CurrentRange" to store that range. I get the error "Run-Time error 1004: Method 'Range' of object'_Global' failed".The rest of my code works when I set the utilization range using the actual range for my purposes, but the problem is the range will change each month and I don't want to change it manually each time. There's a cell in the excel sheet this refers to (AG3) that holds the range value, which in this case is [N7:N75], and I would just like to return that value to the variable "CurrentRange" and set UtilizationRange equal to that.
The value is returned to CurrentRange, but it's the last line that's giving me the problem.
Sub RangeTest()Dim UtilizationRange As Range, Cell As RangeDim CurrentRange As String
CurrentRange = Range("AG3").Value MsgBox (CurrentRange)
'This returns the value [N7:N75], which is what I want
Set UtilizationRange = Range("CurrentRange")End Sub
View 1 Replies
View Related
Mar 16, 2009
I have a filname called ex: "Statusreport xxyyzz.xls"
I'm only interested to put xxyyzz in a variable and reuse it for saving another workbook.
How can I use the different string functions for this. The word "Statusreport" is used for apporx 20 different workbooks.
View 9 Replies
View Related