Parsing a string with variable length delimiters

[hefly]

I have a variable length string.
The beginning of the string begins with text followed by a colon.

I tried to parse this in excel. I exported into an excel spreadsheet, but it converts the sparsed text into a date. Not a text string.

Beginning of the string is as follows with the : as a delimiter, for example:

1-60:
12-60:

The string ends with the text SEC as a delimiter:

SEC 14
SEC 1
SEC 22

How can I parse the beginning and end of my text field with : as a beginning delimiter and SEC as an ending delimiter?

Thank you.

HEFLY

 

[Andrzejek]

So your strings look like:
[tt]
1-60:SEC 14
12-60:SEC 1
13-66:SEC 22
[/tt]
???

Have fun.

---- Andy

 

[hefly]

(1) So I can have a string in a separate field that looks like:

1-60
12-60
13-66

(2) And a field which contains strings that looks like

14
1
22

(3) I also need to

(a) strip the leading charaters before and including the :

And
(b) strip the end of the string that starts with

SEC 14
SEC 1
SEC 22

So all I am left with is what is after the first : and including the very last characters SEC and what follows.

 

[MazeWorX]

How about?

Dim vData as Variant
Dim strMySeparatedString as String

vData = Split(MyString,":")

strMySeparatedString = vData(1)

MyString is the field you want split, just a side note when you split data the first segment is indexed at 0


HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[hefly]

Thank you for trying to answer the question.

The Function stops at strMySeparatedString = vData(1)

Option Compare Database

Function ParseComp3(MyString) As String

Dim vData As Variant
Dim strMySeparatedString As String

vData = Split(MyString, ":")

strMySeparatedString = vData(1)

End Function

 

[MazeWorX]

ParseComp3 = vData(1)

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[hefly]

The code stops at ParseComp3 = vData(1)

Function ParseComp3(MyString) As String

Dim vData As Variant
Dim strMySeparatedString As String
ParseComp3 = vData(1)
vData = Split(MyString, ":")

End Function

 

[MazeWorX]

Function ParseComp3(MyString) As String

    Dim vData As Variant
    Dim strMySeparatedString As Variant

    vData = Split(MyString, ":")
    ParseComp3 = vData(1)

End Function

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Andrzejek]

You can go around and around with this, but hefly did not provide the examples of what string he/she wants to pass to the Function, only the explanation of that may be. Some examples would be nice.

It may or may not contain : (I think, I don't know...)

Have fun.

---- Andy

 

[MazeWorX]

provide an example of the entire string

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[hefly]

Gentlemen: Thank you again for your attempts to solve the problem. Sorry I wasn't more specific. I was hoping to learn how to strip characters from either end of a string using a delimiter.

Let's just simplify and focus on stripping just the first characters from the string.

Let me provide an example of the data that comes out of a tax roll.

13-50:  VariableLengthTextHere:That can contain a colon.
15-59:  VariableLengthTextHere:That can contain a colon.
RIVER Oaks PARK:  LOT 1,   BLOCK 1
Glendale Subdivision:  LOT 1 BLOCK 1
11-22:  VariableLengthTextHere:That can contain a colon.
14-55:  VariableLengthTextHere:That can contain a colon.

Is it possible to strip only those characters preceding the colon where they are numbers? 13-50 Leaving the alpha charagers "River Oaks Park"?

Thank you.

Hefly

 

[Andrzejek]

If you take this string:
[tt]
13-50: VariableLengthTextHere:That can contain a colon.
[/tt]
and Split it by ";", you get
[tt]
Element(0) = "13-50"
Element(1) = "VariableLengthTextHere"
Element(2) = "That can contain a colon."
[/tt]
If you take this:
[tt]
RIVER Oaks PARK: LOT 1, BLOCK 1
[/tt]
and Split it the same way, you get:
[tt]
Element(0) = "RIVER Oaks PARK"
Element(1) = "LOT 1, BLOCK 1"[/tt]

Where do you want to go from here....?

Have fun.

---- Andy

 

[MazeWorX]

is this a single string or multiple examples?

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>