RIGHT function

[paulsolar]

Hi All

I hope there is an easy way of doing this as I have 30,000 cells I need to extract some information from.

In a cell I have some information extracted from an invoice. For example BG12CF942RU, BG15CF96EFDI the information isnt consistent except for one part.

Somewhere in the cells will be a postcode, in the above for examples CF942RU & CF96EF. Luckilly there are only a limited number of opening 2 letters CF NP LD SA & SY , unluckilly there can be 2 or 3 numbers following these opening 2 letters, but after the 2 or 3 numbers there are always 2 letters.

I use the left and right functions quite a lot but this is way beyond my skill set to create a formula to extract the postcode. It may be that it has to be done over 5 columns (one for each of the opening postcodes)

Any help would be really be appreciated.

Cheers

Paul

 

[NARAYANK991]

Re: Using Right function, one for formula gurus

Hi ,

Will there be consistency in the number of characters which precede the postcode , BG12 and BG15 in the cases you have posted ?

Will the number of such prefix characters always be 4 or can it be less or more sometimes ?

 

[SteveO59L]

Re: Using Right function, one for formula gurus

try posting a larger sample of your data, and what you would like to do with it

 

[paulsolar]

Re: Using Right function, one for formula gurus

Hi Both

Many thanks for your quick replies.

There is very little consistency at all with the data. Only the postcode is consistent. The postcode is always two letters, followed by either two of three numbers, followed by two letters. for example BG21CF642SD/A the postcode in this instance is CF642SD. The first two letters of the postcode will always begin with either LD, NP CF, SA or SY

Hope that makes it a little clearer

Kind regards

Paul

[TABLE="width: 163"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: center"]BG12CF832RU[/TD]
[/TR]
[TR]
[TD="align: center"]BG8CF627PZ[/TD]
[/TR]
[TR]
[TD="align: center"]BGLWCF717TN[/TD]
[/TR]
[TR]
[TD="align: center"]BG41CF626JU[/TD]
[/TR]
[TR]
[TD="align: center"]BG22CF628EZ[/TD]
[/TR]
[TR]
[TD="align: center"]BG25CF634ET[/TD]
[/TR]
[TR]
[TD="align: center"]BG3CF465DW[/TD]
[/TR]
[TR]
[TD="align: center"]CRBG3CF465DW[/TD]
[/TR]
[TR]
[TD="align: center"]BG5CF479YN[/TD]
[/TR]
[TR]
[TD="align: center"]BG22NP194LR[/TD]
[/TR]
[TR]
[TD="align: center"]CRBG29SA154DF[/TD]
[/TR]
[TR]
[TD="align: center"]BG7SA131AY[/TD]
[/TR]
[TR]
[TD="align: center"]CRBG7SA131AY[/TD]
[/TR]
[TR]
[TD="align: center"]BG29SA154DF[/TD]
[/TR]
[TR]
[TD="align: center"]BG3CF340JA[/TD]
[/TR]
[TR]
[TD="align: center"]BG16CF399UH[/TD]
[/TR]
[TR]
[TD="align: center"]BG24SA68PB[/TD]
[/TR]
[TR]
[TD="align: center"]BG20SA91AT[/TD]
[/TR]
[TR]
[TD="align: center"]BGBCSA148NB[/TD]
[/TR]
[TR]
[TD="align: center"]BG12SA726JX[/TD]
[/TR]
[TR]
[TD="align: center"]BG29SA650P[/TD]
[/TR]
[TR]
[TD="align: center"]BG8CF434DES[/TD]
[/TR]
[TR]
[TD="align: center"]BG38CF642JY/A[/TD]
[/TR]
[TR]
[TD="align: center"]BG15CF425HE[/TD]
[/TR]
[TR]
[TD="align: center"]BG21CF642SD/A[/TD]
[/TR]
[TR]
[TD="align: center"]BG4CF446UD[/TD]
[/TR]
[TR]
[TD="align: center"]BG26CF433HA[/TD]
[/TR]
[TR]
[TD="align: center"]BG177SA58PN[/TD]
[/TR]
[TR]
[TD="align: center"]BG5CF628BT[/TD]
[/TR]
[TR]
[TD="align: center"]BG57CF447BH[/TD]
[/TR]
[TR]
[TD="align: center"]BG4CF240HQ[/TD]
[/TR]
[TR]
[TD="align: center"]BG28CF425SP[/TD]
[/TR]
[TR]
[TD="align: center"]BG2SA444BZ[/TD]
[/TR]
[TR]
[TD="align: center"]BG15CF356EF[/TD]
[/TR]
[TR]
[TD="align: center"]BGDGCF833EB[/TD]
[/TR]
</tbody>[/TABLE]

 

[NARAYANK991]

Re: Using Right function, one for formula gurus

Hi ,

If you don't mind using helper columns , you can do the following :

1. Let us assume that your data is in the range D2:D36.

2. In E2 , enter the following formula , and copy down :

=LOOKUP(99,FIND({"CF","SA","LD","NP","SY"}, D2))

3. In F2 , enter the following formula , and copy down :

=MAX(FIND("0123456789",D2 & "0123456789"))

4. In G2 , enter the following formula , and copy down :

=MID(D2, E2, F2 - E2 + 2)

 

[paulsolar]

Re: Using Right function, one for formula gurus

Hi

That worked brilliantly, except one litte problem. It doest remove anything after the last two letters of the postcode.

For example BG21CF642SD/A this returns CF642SD/A. Ideally what i need to do now is remove anything after the two letters that are preceeded by the numbers so I'd end up with CF642SD, this would be easy if there were always 3 numbers in the middle, but sometines there are only 2

Kind regards

Paul

 

[NARAYANK991]

Re: Using Right function, one for formula gurus

Hi ,

Sorry. Revise the formulae as follows :

1 and 2 remain the same as in my earlier post.

3. In F2 , enter the following formula , and copy down :

=LOOKUP(9999999, MID(D2, E2+2, {1,2,3,4,5}) + 0)

4. In G2 , enter the following formula , and copy down :

=MID(D2, E2, 2) & F2 & MID(D2, E2+LEN(F2)+ 2,2)

 

[paulsolar]

Re: Using Right function, one for formula gurus

Brilliant thanks, that did it a treat