String Parsing Formula/Function
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
ADVERTISEMENT
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
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
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
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
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
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
Feb 21, 2009
Cell A1 contains 012345678 ** Customer of 987654321 **
Cell B2 contains 087654321
My formula =MID(A1,FIND("of",A1)+2,10) works perfect if it has a "customer of...". How to revise my formula that when it gets to cell B2 and if there is no "** Customer of ..." it would leave it blank or zero rather than #Value!?
View 3 Replies
View Related
Dec 5, 2008
My rows consist of the following cells, an author(s), an article name, a pubilcation name, a volume number, and a page number. There are thousands.
My first column has multiple names and need to separate the names into separate cells. Some names have a comma or semi-column separating them, some have the word "and" separating them. Most of the names are listed as last name, first name. (Some are not and I will have to deal wtih that later.)
Row 1:
Eisenhofer, Jay W., Jordan, Kirk S. Tucker, Marc B."Business Faces Harsh Sentencing Penalties",Delaware Business Review, October 7-13, 1991951
Row 2:
Embley, Craig M., Turezyn, Andrew J. and Welch, Edward P."Recent Developments in Delaware Corporate Law",S.A.S.M.& F., May, 19901443
I'm sure is a multiple part process. Is there a formula for placing anything separated by a comma into a new cell, or placing a tab between the names?
Ultimately this data will be placed in database from which to search for a name and article, etc.
View 9 Replies
View Related
Oct 13, 2009
On sheet 1 I have a list of 1000 firstnames
On sheet 2 I have a list of 1000 emails,
I need a function that states If a cell in the email column contains a string or value from the names column, it will result in a true statement so that I can separate out the emails that have these peoples first names.
View 5 Replies
View Related
Jan 5, 2009
i have a question regarding the right function. can you use a string within the function. i know that the function is written =right(A3,2). is it possible to write the function as =right(a3:z3,2). i keep geting an error with this function
View 4 Replies
View Related
Jan 26, 2007
I have a Sum function in a cell on a spreadsheet and a bit of code which checks it using an If statement. If the Sum equals 1 (100%) it should do one thing, if not do another. However, if I put in the If statement that the Sum cell value should equal 1, it doesn't work. If I put it should equal "1", it does work. Why?
Example that works:
If Range("LocalBirthTypes").Value <> "1" Then
MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool"
Goto Exiting
End If
Example that doesn't work:
If Range("LocalBirthTypes").Value <> 1 Then
MsgBox "To use your own data the split between the birth types must total 100%", vbExclamation, "Maternity service planning tool"
Goto Exiting
End If
View 8 Replies
View Related
Feb 26, 2014
I'm trying to create a formula to return the number of cells where the last 8 characters of a cell end with the string "landline"
My (failed) attempt at creating this is as follows:
=COUNTIF RIGHT(A2:A322,8)="Landline"
View 8 Replies
View Related
Oct 19, 2009
I am trying to make a function to take 3 Cells and using the initial Cell(say A1 with value of 100) as the inital value of a sum. Take Cell two(say A2 with value of 10) and add the value of A1 to Value of A2 in a constant increase. In otherwords A1+A2*A3= next increase. A3 is the value I want to change. But I want to add these values together as many times as the value of A3. so with current numbers will be 100+110+120+130+140 with A3 being a value of 5.
View 7 Replies
View Related
Mar 7, 2014
I would like to express an empty string with the expression .
[Code] .....
=if(D6='';0;1)"
[Code] ....
but I have an error message.
I don't know what is the error?
View 10 Replies
View Related
Jun 10, 2014
I try to merge two sting (one from function). the output of the following sub should be "ttgood". but instead, i got "good" only.
Code:
Function cs()
Dim sv As String
sv = "tt"
End Function
Sub ts()
Dim s As String
s = sv & "good"
MsgBox s
End Sub
View 2 Replies
View Related
May 3, 2007
I have a problem when using the function of text(). I set "=TEXT(SUM(A1:A5), 0) + 5" in Cell A6. Assume the answer of SUM(A1:A5) is 10, the result in A6 will be 15.
My question is how to prohibit a string value [Text(SUM(A1:A5),0)] add a number value [5] to form the result 15.....
View 11 Replies
View Related
Dec 18, 2008
I know I have to use FIND and MID but I can't work out the right syntax: how do I extract Michael or Dave from the following strings:
F - Michael 8735
M - Dave 093
View 3 Replies
View Related
Feb 8, 2007
The following concatenate function was working, then mysteriously stopped. When working it returned the data that I requested, now if I click on any concatenated cell and hit enter I get the function string, see below.
='Input DATA'!C2&" "&'Input DATA'!B2&" "&'Input DATA'!F2
InputData is my tab that is being referenced. All other functions/formulas are working as designed. The data in the referenced cells is still there and I changed from text to number to general, etc but I still get the same result.
View 9 Replies
View Related
Apr 11, 2007
I do not know if this is possible in VBA, but I am hoping it is as it will make my life much easier.
Is it possible to say have a TextBox and a CommandButton, that does the following?
Public Sub Execute()
Dim ObjectName As String
Dim Object As Object
ObjectName = txtObject.Text
Set Object = ObjectName
Object.Execute
End Sub
Or to take it a step further:
Public Sub Execute()
Dim ObjectName As String
Dim FunctionName As String
Dim Object As Object
ObjectName = txtObject.Text
FunctionName = txtFunction.Text
Set Object = ObjectName
Object.FunctionName
End Sub
Are there any inbuilt functions that will create/reference an object based on a string containing the name of the object or function?
View 9 Replies
View Related
Oct 1, 2008
I have a string that I need to sort.
Below code works beautifully but doesn't sort numbers, any advice to get this to sort numbers as well?
Function Alphabetize(ByVal sText As String) As String
Dim sWords() As String, sTemp As String
Dim i As Long, j As Long, n As Long
'-- clean up text
For i = 1 To Len(sText)
Select Case Mid$(sText, i, 1)
Case " ", "a" To "z", "A" To "Z"
Case Else: Mid$(sText, i, 1) = " "
End Select
Next
'-- remove leading and trailing spaces....................
View 9 Replies
View Related
Dec 3, 2009
My knowledge of functions is pretty limited, or negligible to be honest. I require a function to extract numbers from each cell in a selected range.
Eg.
cells contain the following data:
cell 1: xxxxxx 45,59
cell 2: x xxxxx xx 6,45,27
cell 3: x xxxx 28
were x represents text
I need to loop through each cell and extract each of the numbers and list them in a separate range. If applied to the above range of cells the function would show the below in the ouput range. Each number in its own cell.
6
27
28
45
45
59
View 9 Replies
View Related
Sep 28, 2006
The following afterupdate procedure for my txtStart1 text box sends the value to the FormatTimeValue function that is coded in a module.
Private Sub txtStart1_AfterUpdate()
MsgBox Len(Trim(Me.txtStart1.vaue)) '<= outputs 1
Me.txtStart1.Value = FormatTimeValue(Trim(Me.txtStart1.Value))
End Sub
As you can see from the bolded text, the length of the value is 1 when outputted from the forms code.
However, when I pass it to the following function in a module it outputs 2 as the length.
Function FormatTimeValue(vTarget As Integer) As String
Dim TimeStr As String
If IsNumeric(vTarget) Then
MsgBox Len(vTarget) '<= outputs 2
Select Case Len(vTarget)
Case 1 ' e.g., user entered 1 so time should be 01:00
TimeStr = "0" & vTarget & ":00"
View 3 Replies
View Related
Dec 2, 2013
In the attached workbook i need to take out the % numbers from the text to populate the cells.
the Pay % cell is the % before the / and the receive % is after the /.
View 10 Replies
View Related
Dec 15, 2013
I am trying to put text form 3 cells into 1 string. I got the 1st 2 but the 3rd I only want to extra the first part of the cell up to the 1st comma. This is what i have so far:
[Code] ......
For example cell E81 would contain "abc, def, ghi" . I want to add to the string above only "abc" and not the entire cell content.
View 4 Replies
View Related
Jan 28, 2014
I'm trying to come up with a VBA function that would return cell values based on another cell value.
I can do this by comparing a range (column of cells) with the cell value to match and then return the value of the cell next to it.
For instance,
The function should search a column for partial text, as follows:
TS ID
PDT ID
TS 1.1
PDT 1
TS 1.2
PDT 2
TS 1.3
PDT 3
TS 2.1
PDT 4
TS 2.2
PDT 5
TS 3.1
PDT 6
TS 3.2
PDT 7
In the above table, the function should for partial text and return string as follows:
TS covered
PDTs
TS 1.1, TS 1.2, TS 1.3
PDT 1, PDT 2, PDT 3
TS 2.1, TS 2.2
PDT 4, PDT 5
TS 3.1, TS 3.2
PDT 6, PDT 7
So basically I am searching for partial text TS 1. and so on
View 4 Replies
View Related
Mar 10, 2008
I'm currently working on a spreadsheet (see attached) which will have both numeric and text string data. This will be sorted by calender month and linked to another spreadsheet.
Could someone please list 2 functions for me.
The 1st would allow act like a filter allowing only data to be counted for a selected month from a drop down list.
The 2nd allows a counting of pre-set (drop down list) text strings to be counted.
Sorry seems a bit complicated and really I should have either a created a pivot report or used access but I'd like to continue with Excel for this exercise.
View 14 Replies
View Related
Sep 11, 2012
Is it possible to create a string with a range of numbers
1
3
4
5
6
8
10
Result: 1,3:6,8,10
Or
1
2
3
4
Result: 1:4
Or
1
3
5
7
Result: 1,3,5,7
Where consecutive numbers are separated by a ":" and other numbers by a ",". I use the following function to create the ","-concatenate, but I don't know how to create the ":"-ranges.
Function AConcat(a As Variant, Optional Sep As String = "") As String
'By Harlan Grove, March 2002
Dim Y As Variant
If TypeOf a Is Range Then
For Each Y In a.Cells
[Code] .........
View 9 Replies
View Related
