Removing Numbers

[Keith]

I currently have a list of postcodes. As is the nature of
UK codes their length varies. For example:

G3
G45
EH2
EH67

I need to find a way of stripping out the numbers so that
the above would be replaced with

G
G
EH
EH

Can anybody help?

Thanks

 

[GerryK]

=LEFT(A1,MIN(IF(1-ISERROR(FIND({1,2,3,4,5,6,7,8,9},A1)),
FIND({1,2,3,4,5,6,7,8,9},A1)))-1)&
RIGHT(A1,LEN(A1)-MAX(IF(1-ISERROR(FIND
({1,2,3,4,5,6,7,8,9},A1)),
FIND({1,2,3,4,5,6,7,8,9},A1))))

Array entetred.
Returns only the text.

HTH

 

[Bob Phillips]

Keith,

Here is a formula originally posted by George Simms

=LEFT(A2,MATCH(FALSE,ISERROR(1*MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)),0)-1)&
""&RIGHT(A2,MATCH(FALSE,ISERROR(1*MID(A2,MAX(ROW(INDIRECT("1:"&LEN(A2))))-RO
W(INDIRECT("1:"&LEN(A2)))+1,1)),0)-1)

it is an array formula, so commit with Ctrl-Shift-Enter instead of Enter.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Peo Sjoblom]

If the numbers are always to the right this is somewhat shorter

=SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6
,7,8,9},""))))),"")

entered normally

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom