Hi

I have a table with 1000's of records, all of which have a postcode.

I need to determine from each field what the first part of the postcode is (GL1, or L1, SN15), which at first I thought was easy.

But we have 3 different uk postcode lengths (5, 6 or 7), sometimes the first part of the postcode can be 2 digits (E1), 3 digits (GL1) or 4 digits (GL11). 

What i'd like is an another field that sits in the same table, that is an auto calculation that just has that first part of the postcode.

But is hard due to them being different lengths, so simple 'Left' won't work for example.

Also add to the fact that postcodes may not be entered by the customer with correct formatting (without a space), so make it hard to work with a defined postcode length as it may or may not have a space.

So somehow, I think I need a auto calculation field that,

1. Removes the space if there is one in the Postcode field, so we have the true amount of characters in the postcode.

2. The amount of characters in the postcode determines how many of the characters the beginning bit of the postcode is. So if the postcode has 7 characters, it will be the fist 4 characters needed in the calculation result.

If the postcode 6 characters, it will be the first 3 characters needed in the calculation result

If the postcode is 5 characters, it will only be the first  2 characters needed in the calculation result.

 

Hope that makes sense, any help appreciated!

 

 

 

I believe it comes down to =

Let (
chars = Substitute ( Postcode ; " " ; "" )
;
Left ( chars ; Length ( chars ) - 3 )
)

 

Of course its a case of removing the last 3 characters!

Thanks!

4 minutes ago, Chrism said:

Of course its a case of removing the last 3 characters!

Right.

Or, if you could be sure that the space is where it's supposed to be, a case of extracting the first word - which would be even simpler. Perhaps you should consider adding an auto-entered calculation to reformat user entry.

 

The data is entered on a another system and then imported in batches, so we don't have control over hw it is being entered initially.

Following from this, I now create a summary list of this new calculated field, and a 'ValueCount ( FilterValues' of the summary list  to give me a total of postcodes by the region we want. But I have small issue, sometimes I only need to filter values of initial letters of the postcode (GL) but as the calculated fileld contains the full part of the initial postcode (GL1), it misses it. Can the filter values work so it filters if the value is in any part of the text?

So GL would match against GL1, GL2 etc?

 

57 minutes ago, Chrism said:

sometimes I only need to filter values of initial letters of the postcode

From what I see, you actually want to count those values in listOfValues that start with prefix. This could be done simply by:

PatternCount ( ¶ & listOfValues ; ¶ & prefix )

 

57 minutes ago, Chrism said:

The data is entered on a another system and then imported in batches, so we don't have control over hw it is being entered initially.

You could still reformat it as part of the import.

 

Edited March 8, 20223 yr by comment