Removing Numbers

  • Thread starter Thread starter Keith
  • Start date Start date
K

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
 
=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
 
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)
 
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
 
Back
Top