Deleting text (97)

Topic

New Reply

In a field showing postcode information [Postcode], because of typos, some records have two spaces between sections, and some have only one. I want to have only one – is there any easy way to delete out the additional space, given that space 5 should always be free?

Regards. Blitz

Reply | Quote

Replies

Since this is a one-time repair action, it is probably easiest to click in the Postcode field, select Edit/Replace… (Ctrl+H), and replace two spaces by a single space.

Reply | Quote

As there are about 350,000 records, unfortunately this is not an option. I have tried this instead, and it seems to be working:

Function PCodeTidy(TheString)
Dim tmp1 As String
Dim tmp2 As String
Dim tmp3 As String

If (IsNull(TheString)) Then
PCodeTidy = Null
Else
tmp1 = Left(TheString, 4)
tmp2 = Right(TheString, 3)
tmp3 = tmp1 + tmp2
PCodeTidy = tmp3
End If

Reply | Quote

As there are about 350,000 records, unfortunately this is not an option. I have tried this instead, and it seems to be working:

Function PCodeTidy(TheString)
Dim tmp1 As String
Dim tmp2 As String
Dim tmp3 As String

If (IsNull(TheString)) Then
PCodeTidy = Null
Else
tmp1 = Left(TheString, 4)
tmp2 = Right(TheString, 3)
tmp3 = tmp1 + tmp2
PCodeTidy = tmp3
End If

Reply | Quote

Since this is a one-time repair action, it is probably easiest to click in the Postcode field, select Edit/Replace… (Ctrl+H), and replace two spaces by a single space.

Reply | Quote