By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,689 Members | 1,121 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,689 IT Pros & Developers. It's quick & easy.

Extract string before integer

P: 6
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!
Jun 13 '07 #1
Share this Question
Share on Google+
8 Replies

P: 6
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!
Jun 13 '07 #2

FishVal
Expert 2.5K+
P: 2,653
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Text ' to make "a"="A" return true
  3.  
  4. Public Function ExtractLeftString(ByVal varInput) As Variant
  5.  
  6.     Dim intStringCursor As Integer, strChar As String
  7.  
  8.     If IsNull(varInput) Then Exit Function
  9.  
  10.     intStringCursor = 0
  11.  
  12.     Do
  13.         intStringCursor = intStringCursor + 1
  14.         strChar = Mid(varInput, intStringCursor, 1)
  15.     Loop While strChar >= "a" And strChar <= "z"
  16.  
  17.     ExtractLeftString = Left(varInput, intStringCursor - 1)
  18.  
  19. End Function
  20.  
  21.  
Pay attention to code row 1.

Now call this function from SQL query.

Good luck.
Jun 13 '07 #3

Expert 100+
P: 344
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
Expand|Select|Wrap|Line Numbers
  1. Function Startstring(ByVal strInput As String) As String
  2.  
  3. Dim i As Integer, intStop As Integer, strChar As String
  4. intStop = 0
  5. For i = 1 To Len(strInput)
  6.     strChar = Mid(strInput, i, 1)
  7.     If Asc(strChar) > 122 Or Asc(strChar) < 65 Then 'Not A-z
  8.         'need to set intStop at the 1st time only
  9.         If intStop = 0 Then intStop = i
  10.     End If
  11. Next
  12. 'if intStop=0 then no bad chars
  13. 'if intStop=1 then all bad
  14. If intStop = 0 Then
  15.     Startstring = strInput
  16. ElseIf intStop = 1 Then
  17.     Startstring = ""
  18. Else
  19.     Startstring = Left(strInput, intStop - 1)
  20. End If
  21. End Function
  22.  
With a bit of time you can probaly put this in a do loop and exit the loop at the first non-alpha character
Jun 13 '07 #4

Expert 100+
P: 344

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.
Jun 13 '07 #5

10K+
P: 13,262
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.
Jun 13 '07 #6

P: 6
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

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Text ' to make "a"="A" return true
  3.  
  4. Public Function ExtractLeftString(ByVal varInput) As Variant
  5.  
  6.     Dim intStringCursor As Integer, strChar As String
  7.  
  8.     If IsNull(varInput) Then Exit Function
  9.  
  10.     intStringCursor = 0
  11.  
  12.     Do
  13.         intStringCursor = intStringCursor + 1
  14.         strChar = Mid(varInput, intStringCursor, 1)
  15.     Loop While strChar >= "a" And strChar <= "z"
  16.  
  17.     ExtractLeftString = Left(varInput, intStringCursor - 1)
  18.  
  19. End Function
  20.  
  21.  
Pay attention to code row 1.

Now call this function from SQL query.

Good luck.
Jun 14 '07 #7

P: 6
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
Jun 14 '07 #8

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Text ' to make "a"="A" return true
  3.  
  4. Public Function ExtractString(ByVal varInput As Variant, _
  5.                               Optional ByVal varStartSignature) As Variant
  6.  
  7.     Dim intStart As Integer, intStringCursor As Integer, strChar As String
  8.  
  9.     If IsNull(varInput) Then Exit Function
  10.  
  11.     If IsMissing(varStartSignature) Or IsNull(varStartSignature) Then
  12.         intStringCursor = 0
  13.     Else
  14.         intStringCursor = InStr(1, varInput, varStartSignature) + _
  15.                           Len(varStartSignature) - 1
  16.     End If
  17.  
  18.     intStart = intStringCursor + 1
  19.  
  20.     Do
  21.         intStringCursor = intStringCursor + 1
  22.         strChar = Mid(varInput, intStringCursor, 1)
  23.     Loop While strChar >= "a" And strChar <= "z"
  24.  
  25.     ExtractString = Mid(varInput, intStart, intStringCursor - intStart)
  26.  
  27. End Function
  28.  
  29.  
Example

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

Good luck.
Jun 14 '07 #9

Post your reply

Sign in to post your reply or Sign up for a free account.