A couple of text parsing questions

Question no.1

Eg, the following

NORCREST, , WOODSIDE ROAD, , , BANCHORY, KINCARDINESHIRE, AB314EN

How do I get "AB314EN" on its own ie. just the text after the last comma.


Question no.2

Each postcode has either 3/4 characters followed by 3 characters. So in the
above example I need a space to get:

AB31 4EN


I could parse the text in Excel and re-import but I thought I would ask
anyway.

I've done it.

Replace(LTrim(Right([query1].[address],7))," ","")

On Thu, 10 Jul 2008 07:00:00 -0700, scubadiver wrote:

Question no.1

Eg, the following

NORCREST, , WOODSIDE ROAD, , , BANCHORY, KINCARDINESHIRE, AB314EN

How do I get "AB314EN" on its own ie. just the text after the last comma.

Question no.2

Each postcode has either 3/4 characters followed by 3 characters. So in the
above example I need a space to get:

AB31 4EN

I could parse the text in Excel and re-import but I thought I would ask
anyway.

Using Access 2000 or newer:

To get the text after the last comma is easy in a query (it appears
there is actually a comma and space before the final text):

Mid([FieldName],InStrRev([FieldName],",")+2)

To place a space within that last text, for me, it would be best to
write a User Defined Function and call the function from the query.
Copy the below code into a module:

Function GetPostCode(strIn As String) As String
Dim newString As String
newString = Mid(strIn, InStrRev(strIn, ",") + 2)
If Len(newString) = 6 Then
newString = Left(newString, 3) & " " & Right(newString, 3)
Else
newString = Left(newString, 4) & " " & Right(newString, 3)
End If
GetPostCode = newString
End Function
-------------
Then call it from the query:
GetPostCode([FieldName')








--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

You could also have used

InStrRev( address, "," )


to find the position of the last coma (if there is one)


Vanderghast, Access MVP


"scubadiver" wrote in message
...

I've done it.

Replace(LTrim(Right([query1].[address],7))," ","")