extract certain text from a very long string (>1024 characters)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i need to extract some text into a separate field

the field is any length. The required text can be in any position
The required text is in the format kbnnn where nnn is a 1 to 4 digit number
(eg kb2 or kb2044)

Please help
 
Issy,

Try this:

Below is a function that will extract the string you described:
'****Start Function *******
Function ExtractValue(TextString As String) As Variant
Dim varStartOfString As Variant
Dim cntr As Byte
Dim varNxtChrPos As Byte
Dim varNxtChr As Variant
'TextString = "This is the text string that has the value kb2044 just any
where within the string"
varStartOfString = InStr(1, TextString, "kb")
'check to be sure the string "kb" was in the text
If varStartOfString > 0 Then
ExtractValue = "kb"
'get the first number value past the "kb"
varNxtChrPos = varStartOfString + 2
varNxtChr = Mid(TextString, varNxtChrPos, 1)
'start extracting the string
If IsNumeric(varNxtChr) Then
For cntr = 1 To 4
varNxtChr = Mid(TextString, varNxtChrPos, 1)
If varNxtChr >= 0 And varNxtChr <= 9 Then
ExtractValue = ExtractValue & Mid(TextString, varNxtChrPos, 1)
Else
GoTo StopNow
End If
varNxtChrPos = varNxtChrPos + 1
Next cntr
Else
ExtractValue = Null
End If
Else
ExtractValue = Null
End If
StopNow:
End Function

'******End of Code ******

You can call the function from any controls action like:

ExtractValue (the entire string is passed here)

If you wanted to have the extracted value assigned to a variable so you
could use it somewhere you could declare your variable and assign it like:

Dim varMyVal as variant
varMyVal = ExtractValue (the entire string is passed here)
 
Mr B said:
Issy,

Try this:

Below is a function that will extract the string you described:
'****Start Function *******
Function ExtractValue(TextString As String) As Variant
Dim varStartOfString As Variant
Dim cntr As Byte
Dim varNxtChrPos As Byte
Dim varNxtChr As Variant
'TextString = "This is the text string that has the value kb2044 just
any where within the string"
varStartOfString = InStr(1, TextString, "kb")
'check to be sure the string "kb" was in the text
If varStartOfString > 0 Then
ExtractValue = "kb"
'get the first number value past the "kb"
varNxtChrPos = varStartOfString + 2
varNxtChr = Mid(TextString, varNxtChrPos, 1)
'start extracting the string
If IsNumeric(varNxtChr) Then
For cntr = 1 To 4
varNxtChr = Mid(TextString, varNxtChrPos, 1)
If varNxtChr >= 0 And varNxtChr <= 9 Then
ExtractValue = ExtractValue & Mid(TextString,
varNxtChrPos, 1) Else
GoTo StopNow
End If
varNxtChrPos = varNxtChrPos + 1
Next cntr
Else
ExtractValue = Null
End If
Else
ExtractValue = Null
End If
StopNow:
End Function

'******End of Code ******
Click to expand...

You'll need to modify that if the string might contain the string "kb"
*before* the first occurrence of "kb#".
 
Back
Top