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

BG12CF832RU

BG8CF627PZ

BGLWCF717TN

BG41CF626JU

BG22CF628EZ

BG25CF634ET

BG3CF465DW

CRBG3CF465DW

BG5CF479YN

BG22NP194LR

CRBG29SA154DF

BG7SA131AY

CRBG7SA131AY

BG29SA154DF

BG3CF340JA

BG16CF399UH

BG24SA68PB

BG20SA91AT

BGBCSA148NB

BG12SA726JX

BG29SA650P

BG8CF434DES

BG38CF642JY/A

BG15CF425HE

BG21CF642SD/A

BG4CF446UD

BG26CF433HA

BG177SA58PN

BG5CF628BT

BG57CF447BH

BG4CF240HQ

BG28CF425SP

BG2SA444BZ

BG15CF356EF

BGDGCF833EB

<colgroup><col></colgroup><tbody>
</tbody>

 

[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