Custom Format

**ASH2707** · 06-04-2009, 03:46 AM

I apologise in advance if I am duplicating a thread, but haven't been able to find a solution on the forum.

My question if anyone can help;

Within a work sheet I am analysing UK postcode. On the first sheet, the postcode is entered in its entire format ie. AB12 1CD, however on the 2nd sheet, I would like it to copy the 1st sheet but only printing the 1st part ie. AB12

Is there such function that will help me with this?

**sweep** · 06-04-2009, 04:06 AM

You could use a formula to extract the first half of he postcode:

=LEFT(A1,LEN(A1)-3)

**squiggler47** · 06-04-2009, 04:13 AM

This is another variation

=LEFT(P3,FIND(" ",P3)-1)

or

=IF(ISERROR(FIND(" ",P4)),P4,LEFT(P4,FIND(" ",P4)-1))

the second just returns the full post code if there is no space in it in cases of a partial post code!

**DonkeyOte** · 06-04-2009, 04:28 AM

squiggler, you can avoid the need for the Error check by appending the string within the FIND, ie:

=LEFT(P3,FIND(" ",P3&" ")-1)

**squiggler47** · 06-04-2009, 04:34 AM

True!

Still I've got an excuse I only just made my morning coffee!

**DonkeyOte** · 06-04-2009, 04:35 AM

Wasn't having a "go"

Just showing the approach - I always used to use the Error check approach until it was pointed out to me by Richard Schollar, sometimes (read most of the time) the most obvious workarounds are the ones we miss !!