Separate letters and numbers in postcode

[Jon]

Hi, hopefully this one will be an easy one for the excel masters!

Ok - I have a postcode portion

eg. WA1 2TT
eg. W1
eg. N22
eg. RM11

And I want just to return WATT, W, N, RM for e.g. above

Must be easy - surely

 

[kkknie]

Maybe someone else can give you a standard worksheet solution, but
cannot thing of one. You can however do it with a User Define
Function (UDF).

- Open up the Visual Basic Editor (Tools | Macros | Visual Basi
Editor).
- Right click on your workbook in the Project explorer and selec
Insert | Module.
- Paste the following code:

Code
-------------------
Function StripNumbers(xrngInput As Range)

Dim iLen As Long
Dim i As Long
Dim strChar As String
Dim strReturn As String

strReturn = ""
iLen = Len(xrngInput.Value)
For i = 1 To iLen
strChar = Mid(xrngInput.Value, i, 1)
If Not IsNumeric(strChar) And Trim(strChar) <> "" Then
strReturn = strReturn & Mid(xrngInput.Value, i, 1)
End If
Next

StripNumbers = strReturn

End Functio

 

[Bob Phillips]

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

--

HTH

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