I have data in the following string format

BIGGY SBJ LANNA TROXL ETX FLOAT BOYER LRP GEERI KITHE BELAY TRISH CLIPR
ENSUE BAL
or
BIGGY|SBJ|LANNA etc
and

BWZ ELIOT ETX DIMMO DRAPE FORTT DOOTH PSB YNG CXR LEBRN
or
BWZ|ELIOT|ETX... etc..

I am trying to capture the first 5 letter "fix" in the string which is fine
if the first one is the 5 letter "fix". For that i have been using
Left(....,5) obviously.

But i want to be able to say that if the length before the first delimiter
is =3 then check after delimiter to get the first 5 letter "fix".

Unfortunately i cant for the life of me think how to accomplish this.

Any thoughts?

Thanks

Marcus



but also with

Try this (where I have assumed YourText contains your data)...

If Mid(YourText, 3, 1) Like "[ |]" Then
CapturedText = Mid(YourText, 4, 5)
Else
CapturedText = Left(YourText, 5)
End If

Rick


"mebsmith" wrote in message
...
I have data in the following string format

BIGGY SBJ LANNA TROXL ETX FLOAT BOYER LRP GEERI KITHE BELAY TRISH CLIPR
ENSUE BAL
or
BIGGY|SBJ|LANNA etc
and

BWZ ELIOT ETX DIMMO DRAPE FORTT DOOTH PSB YNG CXR LEBRN
or
BWZ|ELIOT|ETX... etc..

I am trying to capture the first 5 letter "fix" in the string which is
fine
if the first one is the 5 letter "fix". For that i have been using
Left(....,5) obviously.

But i want to be able to say that if the length before the first delimiter
is =3 then check after delimiter to get the first 5 letter "fix".

Unfortunately i cant for the life of me think how to accomplish this.

Any thoughts?

Thanks

Marcus



but also with

maybe try something like this:

=IF(FIND(" ",A1,1)<5,MID(A1,FIND(" ",A1,1)+1,5),LEFT(A1,5))


"mebsmith" wrote:

I have data in the following string format

BIGGY SBJ LANNA TROXL ETX FLOAT BOYER LRP GEERI KITHE BELAY TRISH CLIPR
ENSUE BAL
or
BIGGY|SBJ|LANNA etc
and

BWZ ELIOT ETX DIMMO DRAPE FORTT DOOTH PSB YNG CXR LEBRN
or
BWZ|ELIOT|ETX... etc..

I am trying to capture the first 5 letter "fix" in the string which is fine
if the first one is the 5 letter "fix". For that i have been using
Left(....,5) obviously.

But i want to be able to say that if the length before the first delimiter
is =3 then check after delimiter to get the first 5 letter "fix".

Unfortunately i cant for the life of me think how to accomplish this.

Any thoughts?

Thanks

Marcus



but also with

Hi,

If you want to return the first occurrence of a praticular word length then
try this UDF.

Alt+F11 to open VB editor. Right click 'This Workbook' and insert module and
paste the code below in

Call with

=Strlength(5,a1)

where 5 is the string length of word you want and a1 contains the string

Function Strlength(length As Long, str As Range) As String
Strlength = ""
For i = 1 To Len(str)
Strlength = Strlength & Mid(str, i, 1)
If Mid(str, i, 1) = " " Then
If Len(Trim(Strlength)) = length Then
Exit For
Else
Strlength = ""
End If
End If
Next i
If Len(Trim(Strlength)) < length _
Then Strlength = "No Match found"
End Function


Mike


"mebsmith" wrote:

I have data in the following string format

BIGGY SBJ LANNA TROXL ETX FLOAT BOYER LRP GEERI KITHE BELAY TRISH CLIPR
ENSUE BAL
or
BIGGY|SBJ|LANNA etc
and

BWZ ELIOT ETX DIMMO DRAPE FORTT DOOTH PSB YNG CXR LEBRN
or
BWZ|ELIOT|ETX... etc..

I am trying to capture the first 5 letter "fix" in the string which is fine
if the first one is the 5 letter "fix". For that i have been using
Left(....,5) obviously.

But i want to be able to say that if the length before the first delimiter
is =3 then check after delimiter to get the first 5 letter "fix".

Unfortunately i cant for the life of me think how to accomplish this.

Any thoughts?

Thanks

Marcus



but also with

One way is to use a function like...

Public Function GetFix(ByVal String1 As String, _
ByVal nFix As Long) As String
Dim dLim As String
Dim arrSplit() As String
Dim i As Long

If InStr(String1, "|") Then
dLim = "|"
Else
dLim = " "
End If

arrSplit() = Split(String1, dLim)

GetFix = ""

For i = 0 To UBound(arrSplit)
If Len(arrSplit(i)) = nFix Then
GetFix = arrSplit(i)
Exit For
End If
Next i
End Function


.... you would then call this from within a procedure - e.g.

MyFixString = GetFix("ANY STRIN GYO ULIKE", 5)

Assumptions:
The delimiter will either be a pipe "|" or a space " ".
Only one type of delimiter will be used in any given String1
argument

Good luck ?:^)

NickH

On Thu, 30 Oct 2008 06:21:00 -0700, mebsmith
wrote:

I have data in the following string format

BIGGY SBJ LANNA TROXL ETX FLOAT BOYER LRP GEERI KITHE BELAY TRISH CLIPR
ENSUE BAL
or
BIGGY|SBJ|LANNA etc
and

BWZ ELIOT ETX DIMMO DRAPE FORTT DOOTH PSB YNG CXR LEBRN
or
BWZ|ELIOT|ETX... etc..

I am trying to capture the first 5 letter "fix" in the string which is fine
if the first one is the 5 letter "fix". For that i have been using
Left(....,5) obviously.

But i want to be able to say that if the length before the first delimiter
is =3 then check after delimiter to get the first 5 letter "fix".

Unfortunately i cant for the life of me think how to accomplish this.

Any thoughts?

Thanks

Marcus



but also with

It appears as if you will be parsing out flight plans, which may lead you into
a requirement for more than a "one-off" solution.

So long as the strings will be less than 256 characters, one simple way would
be to download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

and then use this formula which will return the first 5 letter string
consisting of all capital letters.

=REGEX.MID(A1,"[A-Z]{5}")

=REGEX.MID(A1,"[A-Z]{5}",2) would return the second 5 letter string, etc.

You can also easily modify the pattern to detect Victor airways, Jet routes, Q
routes, etc, if that is something you will be getting into.

If the strings might be longer than 255 characters, this function can be
written as a UDF.
--ron

Ron i am interested in identifying strings for initial fixes but your link is
broken.

"Ron Rosenfeld" wrote:

On Thu, 30 Oct 2008 06:21:00 -0700, mebsmith
wrote:

I have data in the following string format

BIGGY SBJ LANNA TROXL ETX FLOAT BOYER LRP GEERI KITHE BELAY TRISH CLIPR
ENSUE BAL
or
BIGGY|SBJ|LANNA etc
and

BWZ ELIOT ETX DIMMO DRAPE FORTT DOOTH PSB YNG CXR LEBRN
or
BWZ|ELIOT|ETX... etc..

I am trying to capture the first 5 letter "fix" in the string which is fine
if the first one is the 5 letter "fix". For that i have been using
Left(....,5) obviously.

But i want to be able to say that if the length before the first delimiter
is =3 then check after delimiter to get the first 5 letter "fix".

Unfortunately i cant for the life of me think how to accomplish this.

Any thoughts?

Thanks

Marcus



but also with

It appears as if you will be parsing out flight plans, which may lead you into
a requirement for more than a "one-off" solution.

So long as the strings will be less than 256 characters, one simple way would
be to download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr/

and then use this formula which will return the first 5 letter string
consisting of all capital letters.

=REGEX.MID(A1,"[A-Z]{5}")

=REGEX.MID(A1,"[A-Z]{5}",2) would return the second 5 letter string, etc.

You can also easily modify the pattern to detect Victor airways, Jet routes, Q
routes, etc, if that is something you will be getting into.

If the strings might be longer than 255 characters, this function can be
written as a UDF.
--ron

On Thu, 6 Nov 2008 07:37:35 -0800, mebsmith
wrote:

Ron i am interested in identifying strings for initial fixes but your link is
broken.

I've noticed that link is intermittently broken. You can search for
morefunc.xll using Google, and there may be other download sources.

Or you can just use your own UDF to mimic most of the features.

The following can be used with the same instructions that I outlined previously
(and won't have the 255 character limit).

To enter this UDF, <alt-F11 opens the VB Editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

The use is the same, except the function will be called RegexMid instead of
Regex.Mid

In other words:

for the first intersection:
=REGEXMID($A1,"\b[A-Z]{5}\b")

for the 2nd intersection:
=REGEXMID($A1,"\b[A-Z]{5}\b",2)

For more information regarding the syntax for the Pattern argument, either ask
here for specific patterns to match, or read he

http://msdn.microsoft.com/en-us/libr...b2(VS.85).aspx

================================================== ========
Option Explicit
Function RegexMid(Str As String, Pattern As String, _
Optional Index As Variant = 1, _
Optional CaseSensitive As Boolean = True, _
Optional MultiLin As Boolean = False) _
As Variant 'Variant as value may be string or array


Dim objRegExp As Object
Dim objMatch As Object
Dim colMatches As Object

Dim i As Long 'counter
Dim T() As String 'container for array results

' Create a regular expression object.
Set objRegExp = CreateObject("vbscript.regexp")

'Set the pattern by using the Pattern property.
objRegExp.Pattern = Pattern

' Set Case Insensitivity.
objRegExp.IgnoreCase = Not CaseSensitive

'Set global applicability.
objRegExp.Global = True

'Set multiline
objRegExp.MultiLine = MultiLin

'Test whether the String can be compared.
If (objRegExp.Test(Str) = True) Then

'Get the matches.
Set colMatches = objRegExp.Execute(Str) ' Execute search.

On Error Resume Next 'return null string if a colmatch index is non-existent
If IsArray(Index) Then
ReDim T(1 To UBound(Index))
For i = 1 To UBound(Index)
T(i) = colMatches(Index(i) - 1)
Next i
RegexMid = T()
Else
RegexMid = CStr(colMatches(Index - 1))
If IsEmpty(RegexMid) Then RegexMid = ""
End If
On Error GoTo 0 'reset error handler
Else
RegexMid = ""
End If
End Function
================================================
--ron