how to Get all Strings Except last string in comma sepated string

[narasappaj]

Hi Experts,

I have list of addresses, which contains Address line1,Addr Line2(optional),Addr Line3(optional),Town,Postal Code.

I want to extract all data except Postal Code.
My Addresses are like

5 Bridge Street, Wisbech, PE13 1AF
81 High Street, Holbeach, Spalding, PE12 7ED
The Old Vicarage, Capel Bangor, Aberystwyth, SY23 1LZ
85 Uxbridge Road, London, W5 5TH
Marringdean Road, Billingshurst, RH14 9HD

i tried With
=TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",LEN(A1))),LEN(A1))) which returns only first text,
and here no of commas are not fixed


How can i do this ?

Thanks
Narasappa

[Special-K]

Try
=LEFT(TRIM(LEFT(A1,LEN(A1)-8)),LEN(TRIM(LEFT(A1,LEN(A1)-8)))-1)

UPDATE: It'll will remove the last letter if the postcode is 6 characters, ie W5 5TH

[Special-K]

This fixes the problem entirely.

=LEFT(TRIM(LEFT(A1,LEN(A1)-8)),LEN(TRIM(LEFT(A1,LEN(A1)-8)))-IF(RIGHT(TRIM(LEFT(A1,LEN(A1)-8)),1)=",",1,0))

[narasappaj]

Many Thanks @ Special-K