Function to select just letters in a cell?

[aselldurn]

Hi,

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!

 

[Barry Katcher]

Strange thing happening. I set up Hotpepper's exdata() function in a standard module in a new workbook and it works perfectly. I then copied it to a standard module in my PERSONAL macro workbook and, when I enter the formula in a cell, I get a Compile Error:

Can't find project or library

and the code window shows:

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))
.........

with the first line arrowed and background-highlighted in yellow and with the "Mid" in line 8 background-highlighted in blue.

I read the "Help":

"A referenced project could not be found, or a referenced object library corresponding to the language of the project could not be found."

but I'm not a coder and don't understand what it's trying to tell me. Can anyone clarify? Why is it working in a new workbook and not in my PERSONAL workbook?

 

[Aladin Akyurek]

If you already have installed the morefunc.xll add-in:

=REGEX.SUBSTITUTE(A2,"[0123456789]","")

 

[Barry Katcher]

Hi, Aladin. Thanks for the response.

Yes, I do have morefunc, but what I am trying to do is to get Hotpepper's function to work. His function allows for the extraction of the different types of characters, alone or in combination, by just changing the second argument.

As I said, I've got it working in a new workbook, but can't seem to get it to work when I place it in my PERSONAL macro workbook, as per my above post. I can't understand why it works in one book and not in the other.

 

[Scott Huish]

Try saving it as an add-in and loading it that way.

 

[Barry Katcher]

What's the procedure for that? Something in Tools>Add-Ins>Browse?

 

[Scott Huish]

Put the code in a standard module in a blank workbook.
Close the VBE and save it as an add-in (In XL2002, it's the last choice for file type and has an xla extension), somewhere where you're not going to move the file. Close the Workbook.

Then when you have a worksheet open, you should be able to go to Tools>Add-Ins>Browse to load it.

 

[Barry Katcher]

Thanks, Hotpepper. Spot on, as the Brits say. I did as you said and now I can call the function from any workbook I open.

Two further questions/clarifications:

1. This is an add-in on my computer only. If I use it in a workbook that will be on a network drive, the end-users at other stations will still get a #NAME! error. I'll first have to Copy>Paste Special>Values the results before I place it on the network. Am I correct? If so, I guess I can still place the code in the individual workbook.

2. Is there any way to get this function to appear on the Functions (fx) Wizard's "Function Name" list in the "Paste Function" drop-down dialogue box?

 

[Scott Huish]

I think you should be able to put the code in a standard module within the workbook, so you don't have that problem.

In the function wizard, you should be able to find it under All or User Defined Functions (S=string, O=option)

 

[Barry Katcher]

Duh! There it was, under "User Defined". Duh!