Extract Text (2003)

Topic

New Reply

I have a various strings of text in the format of ‘abcdef V abc’. The number of characters before, and after the V, can vary. I want to create two fields. One containing the text before the V, and another one after. How do I do this, when I can’t determine the length of the string?

Reply | Quote

Replies

Create a query based on the table. Add two calculated columns:

FirstPart: Left([FieldName], InStr([FieldName], ” V “)-1)

and

LastPart: Mid([FieldName], InStr([FieldName], ” V “)+3)

where FieldName is the name of your field.

Reply | Quote

Thanks Hans.

Reply | Quote

I’ve just discovered that some of the text fields have nothing before the V, and some nothing after. This is bringing up and error. Is there a simple way round this?

Reply | Quote

Try these considerably more complicated expressions:

FirstPart: IIf(Left([FieldName],2)=”V “,Null,IIf(Right([FieldName],2)=” V”,Left([FieldName],InStr([FieldName],” V”)-1),Left([FieldName],InStr([FieldName],” V “)-1)))

LastPart: IIf(Right([FieldName],2)=” V”,Null,IIf(Left([FieldName],2)=”V “,Mid([FieldName],3),Mid([FieldName],InStr([FieldName],” V “)+3)))

Reply | Quote

Thanks Hans. It’s working fine, for the moment.

Reply | Quote

A general solution to this sort of problem are the following 2 functions which return the first part of a string up to a defined character or characters & the part after the character or characters.

Function GetFirstWord(strIn As String, chrDelimit As String) As String
‘ Parameters : strIn – string to search
‘ chrDelimit – character delimiter
‘ if delimiter is not found whole string is returned
Dim strTmp As String
Dim intPos As Integer
Dim strFirstName
Dim strLastName

strTmp = Trim$(strIn)
intPos = InStr(strTmp, chrDelimit)

If intPos = 0 Then
GetFirstWord = strTmp
Else
GetFirstWord = left$(strTmp, intPos – 1)
strLastName = Mid(strTmp, intPos + 1)
End If

End Function

Function GetLastWord(strIn As String, chrDelimit As String) As String
‘ Parameters : strIn – string to search
‘ chrDelimit – character delimiter
‘ if delimiter is not found null is returned
Dim strTmp As String
Dim intPos As Integer
Dim strFirstName
Dim strLastName

strTmp = Trim$(strIn)
intPos = InStr(strTmp, chrDelimit)

If intPos = 0 Then
GetLastWord = “”
Else
GetLastWord = Mid(strTmp, intPos + Len(chrDelimit))
End If

End Function

Reply | Quote