Extract string before integer

[zadkiel]

hi all.

I got a urgent problem in my job.

the sample data as follows:

FT="EXERPRI:$68 .88/10W*BB9505-28765710"
FT="MEETON6/3/07FOR

What I need is to extract the string after the quote and before any punctuation or integer so my outcome can be like this:

EXERPRI
MEETON

I use the SQL query as follows

SELECT Left([freetext],InStr([freetext],isnumeric())-1) AS Expr1
FROM Feb;

but it doesn't work. What can I do? Thanks a lot!

[zadkiel]

Extract Substrings in Strings before other datatypes

hi all

I got a urgent problem in my job.

the sample data as follows:

FT="EXERPRI:$68 .88/10W*BB9505-28765710"
FT="MEETON6/3/07FOR

What I need is to extract the string after the quote and before any punctuation or integer so my outcome can be like this:

EXERPRI
MEETON

I use the SQL query as follows

SELECT Left([freetext],InStr([freetext],isnumeric())-1) AS Expr1
FROM Feb;

but it doesn't work. What can I do? Thanks a lot!

[FishVal]

hi all.

I got a urgent problem in my job.

the sample data as follows:

FT="EXERPRI:$68 .88/10W*BB9505-28765710"
FT="MEETON6/3/07FOR

What I need is to extract the string after the quote and before any punctuation or integer so my outcome can be like this:

EXERPRI
MEETON

I use the SQL query as follows

SELECT Left([freetext],InStr([freetext],isnumeric())-1) AS Expr1
FROM Feb;

but it doesn't work. What can I do? Thanks a lot!

Hi!

I can suggest the following.

Place the code below to a public module

[CODE=vb]

Option Compare Text ' to make "a"="A" return true

Public Function ExtractLeftStri ng(ByVal varInput) As Variant

Dim intStringCursor As Integer, strChar As String

If IsNull(varInput ) Then Exit Function

intStringCursor = 0

Do
intStringCursor = intStringCursor + 1
strChar = Mid(varInput, intStringCursor , 1)
Loop While strChar >= "a" And strChar <= "z"

ExtractLeftStri ng = Left(varInput, intStringCursor - 1)

End Function

[/CODE]

Pay attention to code row 1.

Now call this function from SQL query.

Good luck.

[Lysander]

hi all.

I got a urgent problem in my job.

the sample data as follows:

FT="EXERPRI:$68 .88/10W*BB9505-28765710"
FT="MEETON6/3/07FOR

What I need is to extract the string after the quote and before any punctuation or integer so my outcome can be like this:

EXERPRI
MEETON

I use the SQL query as follows

SELECT Left([freetext],InStr([freetext],isnumeric())-1) AS Expr1
FROM Feb;

but it doesn't work. What can I do? Thanks a lot!

This function is quick and dirty but it works

Function Startstring(ByVal strInput As String) As String

Dim i As Integer, intStop As Integer, strChar As String
intStop = 0
For i = 1 To Len(strInput)
    strChar = Mid(strInput, i, 1)
    If Asc(strChar) > 122 Or Asc(strChar) < 65 Then 'Not A-z
        'need to set intStop at the 1st time only
        If intStop = 0 Then intStop = i
    End If
Next
'if intStop=0 then no bad chars
'if intStop=1 then all bad
If intStop = 0 Then
    Startstring = strInput
ElseIf intStop = 1 Then
    Startstring = ""
Else
    Startstring = Left(strInput, intStop - 1)
End If
End Function

With a bit of time you can probaly put this in a do loop and exit the loop at the first non-alpha character

[Lysander]

Pay attention to code row 1.

Now call this function from SQL query.

Good luck.

Hi FishVal, guess we both answered the same problem at the same time. Your solution is neater though, like it.

[r035198x]

hi all.

I got a urgent problem in my job.

the sample data as follows:

FT="EXERPRI:$68 .88/10W*BB9505-28765710"
FT="MEETON6/3/07FOR

What I need is to extract the string after the quote and before any punctuation or integer so my outcome can be like this:

EXERPRI
MEETON

I use the SQL query as follows

SELECT Left([freetext],InStr([freetext],isnumeric())-1) AS Expr1
FROM Feb;

but it doesn't work. What can I do? Thanks a lot!

@OP: Next time please don't double post.

Threads merged.

[zadkiel]

Thanks a lot but the result comes in this way:

FT
FT

it only captures FT...Frustrated

Hi!

I can suggest the following.

Place the code below to a public module

[CODE=vb]

Option Compare Text ' to make "a"="A" return true

Public Function ExtractLeftStri ng(ByVal varInput) As Variant

Dim intStringCursor As Integer, strChar As String

If IsNull(varInput ) Then Exit Function

intStringCursor = 0

Do
intStringCursor = intStringCursor + 1
strChar = Mid(varInput, intStringCursor , 1)
Loop While strChar >= "a" And strChar <= "z"

ExtractLeftStri ng = Left(varInput, intStringCursor - 1)

End Function

[/CODE]

Pay attention to code row 1.

Now call this function from SQL query.

Good luck.

[zadkiel]

oh I changed
intStringCursor = 5
then it works PERFECTLY WELL!!!!

THANKS A LOT!!!!!!!!!!

Thanks a lot but the result comes in this way:

FT
FT

it only captures FT...Frustrated

[FishVal]

oh I changed
intStringCursor = 5
then it works PERFECTLY WELL!!!!

THANKS A LOT!!!!!!!!!!

Glad it was helpful.

You should replace
intStringCursor = 5
with
intStringCursor = 4

otherwise
ExtractLeftStri ng ("FT=""12312 ")
will return
FT="1
instead of
FT="

Code below is more universal.

[CODE=vb]

Option Compare Text ' to make "a"="A" return true

Public Function ExtractString(B yVal varInput As Variant, _
Optional ByVal varStartSignatu re) As Variant

Dim intStart As Integer, intStringCursor As Integer, strChar As String

If IsNull(varInput ) Then Exit Function

If IsMissing(varSt artSignature) Or IsNull(varStart Signature) Then
intStringCursor = 0
Else
intStringCursor = InStr(1, varInput, varStartSignatu re) + _
Len(varStartSig nature) - 1
End If

intStart = intStringCursor + 1

Do
intStringCursor = intStringCursor + 1
strChar = Mid(varInput, intStringCursor , 1)
Loop While strChar >= "a" And strChar <= "z"

ExtractString = Mid(varInput, intStart, intStringCursor - intStart)

End Function

[/CODE]

Example

? ExtractString(" FT=""qwerty123" , "FT=""")
qwerty
? ExtractString(" AnyName=""qwert y123", "AnyName="" ")
qwerty
? ExtractString(" qwerty123")
qwerty

Good luck.