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]

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

 
Option Compare Text ' to make "a"="A" return true
 
Public Function ExtractLeftString(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"
 
    ExtractLeftString = Left(varInput, intStringCursor - 1)
 
End Function
 
 

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

 
Option Compare Text ' to make "a"="A" return true
 
Public Function ExtractLeftString(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"
 
    ExtractLeftString = Left(varInput, intStringCursor - 1)
 
End Function
 
 

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
ExtractLeftString ("FT=""12312")
will return
FT="1
instead of
FT="

Code below is more universal.

 
Option Compare Text ' to make "a"="A" return true
 
Public Function ExtractString(ByVal varInput As Variant, _
                              Optional ByVal varStartSignature) As Variant
 
    Dim intStart As Integer, intStringCursor As Integer, strChar As String
 
    If IsNull(varInput) Then Exit Function
 
    If IsMissing(varStartSignature) Or IsNull(varStartSignature) Then
        intStringCursor = 0
    Else
        intStringCursor = InStr(1, varInput, varStartSignature) + _
                          Len(varStartSignature) - 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
 
 

Example

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

Good luck.