A couple of text parsing questions

  • Thread starter Thread starter scubadiver
  • Start date Start date
S

scubadiver

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.
 
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.
Click to expand...

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
 
You could also have used

InStrRev( address, "," )


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


Vanderghast, Access MVP
 
Back
Top