Function to select just letters in a cell?

aselldurn

New Member
Joined
Oct 7, 2004
Messages
12
Hi, :help:

Does anyone know if there is a function that will just select the alphabetic characters from a cell? I want to use it to get alphabetic part of a postcode, so for example CM19 would become CM, E13 would become E etcetc....I'm using Excel 2002.

Thanks!
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
I don't know of a specific function, but if you only want the first Alpha part of a postcode then you could use
=LEFT(A1,1)&IF(ISNUMBER(--MID(A1,2,1)),"",MID(A1,2,1))
This does assume that the post code only has a maximum of two letters at the start, which I think is right, but I don't know all the postcodes!

HTH

GaryB
 
Upvote 0
Thanks both for your help. I've used the isnumber formula already to solve it but I just wondered if the there was one function that already exists that does it for me, just cos I'm always on the look out for new functions!
 
Upvote 0
Book1
ABCD
1CM19CM
2E13E
3ABC123ABC
4
5
Sheet1


Try…

In Cell A1 enter :

=LEFT(A1,MIN(FIND("~",SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},"~")&"~"))-1)

Regards
 
Upvote 0
If you're interested, this is an updated version of the function I wrote and posted in this post:
http://www.mrexcel.com/board2/viewtopic.php?t=104395

Code:
Public Function exdata(s As String, Optional o As Integer = 0) As Variant
Dim a As String, y As Integer

If o = 0 Then
    exdata = s
    Exit Function
End If

For y = 1 To Len(s)
Select Case Asc(Mid(s, y, 1))
    Case 48 To 57
        If o = 1 Or o = 4 Then a = a & Mid(s, y, 1)
    Case 65 To 90, 97 To 122
        If o = 2 Or o = 3 Or o = 4 Then a = a & Mid(s, y, 1)
    Case Else
        If o = 2 Then a = a & Mid(s, y, 1)
End Select
Next y

Select Case o
    Case 1
        exdata = --a
    Case 2 To 4
        exdata = a
End Select
End Function
exdata.xls
ABCDE
112345//98ABC123Usageis:=exdata(rangeorstring,typeofextraction)
2Defaulttypeofextractionis0,
3iftypeofextractionismissing,entirestringisreturned.
4
5Typeofextractioncanbeavaluefrom0(default)to4
60=entirestring,1=numbers,2=alltext
73=alphabeticalcharacters,4=alphanumericcharacters
8
9
10Examples:=exdata(A1,0)returnsentirestring12345//98ABC123
11=exdata(a1,1)extractsallnumbers1234598123
12=exdata(a1,2)extractsalltext//ABC
13=exdata(a1,3)returnsallalphabeticalcharactersABC
14=exdata(a1,4)returnsallalphanumericcharacters1234598ABC123
15=exdata("ABC123!",1)returns123123
Sheet1
 
Upvote 0
Hotpepper, I'm trying to set up and use your function. I pasted it into "This workbook" module, entered the formla in a cell, and got a #NAME? error. It appears I'm missing something or doing something wrong (imagine). Can you help?
 
Upvote 0
Barry,

Put it in a standard module. Won't work in a sheet or Thisworkbook module.
 
Upvote 0
Hi

This is to eliminate all the numbers from the string
Code:
Function strings(txt As String) As String
With CreateObject("VBScript.RegExp")
    .Pattern = "\d{1,}"
    If .Test(txt) Then
        Set mItem = .Execute(txt)
        For Each m In mItem
            txt = Replace(txt, m, "")
        Next
        strings = txt
    End If
End With
End Function
 
Upvote 0

Forum statistics

Threads
1,224,559
Messages
6,179,513
Members
452,921
Latest member
BBQKING

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top