just extract the alphanumeric characters

[vacation]

Hi,

In column C, there are over 30,000 items of data that look like this:

...
R#CS2719==
R#CS2653=
R#CS2720=
R#CS2654=
R#CS2770===
BMHH235.5===#
BMFA63023.1=%
BMFA63023===)
BMHP286.1===$
BMHP286=====!
BMRS192A1===~
BMFD280.1===#
BMFD281.1===#
BMHD196======
BMRS283=====(
BMH34641====~
...

They contain all kinds of non-alphanumeric characters.
Note: All possible non-alphanumeric characters are not shown above.
The above is just a small sample.

I would like column E to contain data from the corresponding cells in
column C but exclude all the non-alphanumeric characters.
So the above column C data would appear in column E as:

...
RCS2719
RCS2653
RCS2720
RCS2654
RCS2770
BMHH2355
BMFA630231
BMFA63023
BMHP2861
BMHP286
BMRS192A1
BMFD2801
BMFD2811
BMHD196
BMRS283
BMH34641
...


What combination of nested functions should I assign to each cell in column E to achieve this?


Thanks.

 

[Whisperer14]

Dear All,

Yet another field to learn. Thank you for an interesting post.

Gordon

 

[parry]

Thanks again, brettdj. Excellent info.

I'd tried using [A-Z] at some point, but of course it didn't work.

Yogi! That's the kind of function I was looking for before I wrote my code!!

I kept thinking if there is an IsNumeric function there ought to be an IsAlpha function...No such thing. I can't imagine why.

This turned out to be a very informative post for me. I hope vacation got the help s/he needs.

Thank you very much.

Merry CHRISTmas!!

Tana-Lee

Hi Tana, you can use an A-Z type of statement but I can only get this to work in a Select Case situation. Heres Yogi's code without using ASCII characters.

Sub NonASCIIVersion()
Dim cell, j As Integer, ylet As String
    For Each cell In Intersect(ActiveSheet.UsedRange, Columns("c"))
        For j = 1 To Len(cell)
            Select Case Mid(cell, j, 1)
            Case "A" To "Z", "a" To "z", "0" To "9"
            ylet = ylet & Mid(cell, j, 1)
            End Select
        Next j
        cell.Offset(0, 2) = ylet
        ylet = ""
    Next cell
End Sub

 

[Nimrod]

Hello Dave:
Thanks for giving me a hand on understanding your code , I certainly appreciate it . I'm on the road during the xmas holidays so won't really get a chance to play with it for a few days. Again thx for the help ray:

Cheers Mate :

 

[Yogi Anand]

Hi Parry:

Very Nice -- Thanks!

....
Hi Tana, you can use an A-Z type of statement but I can only get this to work in a Select Case situation. Heres Yogi's code without using ASCII characters.

Sub NonASCIIVersion()
Dim cell, j As Integer, ylet As String
    For Each cell In Intersect(ActiveSheet.UsedRange, Columns("c"))
        For j = 1 To Len(cell)
            Select Case Mid(cell, j, 1)
            Case "A" To "Z", "a" To "z", "0" To "9"
            ylet = ylet & Mid(cell, j, 1)
            End Select
        Next j
        cell.Offset(0, 2) = ylet
        ylet = ""
    Next cell
End Sub

 

[parry]

Hi Parry:

Very Nice -- Thanks!

Your welcome Yogi, I owe you a bunch after the help you have given me on various things.

Like the others in this post I was interested in the use of Regular Expressions in VbScript. I didnt realize you could use scripting in VBA. It seems you could also do this with Jscript as well.

 

[vacation]

Thank you ALL - for your contributions and suggestions.
I learnt a lot more than I expected.
I appreciate it.

 

[Tana Lee]

Wow, what a great thread!

This one has several posts that should definitely be put into the Hall of Fame.

Just EXCELLENT posts!!

Thanks!!

Tana-Lee

 

[brettdj]

Tusharm wrote

As Dave demonstrated, an overlooked capability is the use of Regular Expressions. To use a regexp embedded in a UDF see my post in http://www.mrexcel.com/board2/viewtopic.php?t=52283&start=10

The functions in this thread are extremely useful, I'll certainly be using them . Thanks very much for this Tusharm

Have you thought about adding the ability to the RegExpFind function to return all matches or any particular match rather than the first?

When I tested them I initially got an error as I forgot to set the reference required by the early binding. If anyone else trys using these functions it is necessary to set a reference to the Microsoft VBscript Regular Expressions 5.5 Library via the Tools - References menu in the VBE.

I tend to use late binding (ie Dim RegExp then use CreateObject etc), not the best way in a purist sense but I had too many problems with people getting errors when they hadn't set the reference properly - my bad explanation no doubt.

Cheers

Dave

 

[tusharm]

You are welcome, Dave.

RegExpFind does return all matches. Use it as an array formula. For example if F15 contains 9809abc123, then select G15:H15 and array-enter =RegExpFind(F15,"(\d+)+") to get both sets of numbers.

To array enter a formula don't complete data entry with ENTER. Instead use CTRL+SHIFT+ENTER.

As far as early/late binding goes, I do all my development with early binding. That way I have access to VBE's intellisense capability. I'd be lost without it and online help. Typically, but not always, I use a compile-time constant to switch to late-binding for released software.

Tusharm wrote

As Dave demonstrated, an overlooked capability is the use of Regular Expressions. To use a regexp embedded in a UDF see my post in http://www.mrexcel.com/board2/viewtopic.php?t=52283&start=10

The functions in this thread are extremely useful, I'll certainly be using them . Thanks very much for this Tusharm

Have you thought about adding the ability to the RegExpFind function to return all matches or any particular match rather than the first?

When I tested them I initially got an error as I forgot to set the reference required by the early binding. If anyone else trys using these functions it is necessary to set a reference to the Microsoft VBscript Regular Expressions 5.5 Library via the Tools - References menu in the VBE.

I tend to use late binding (ie Dim RegExp then use CreateObject etc), not the best way in a purist sense but I had too many problems with people getting errors when they hadn't set the reference properly - my bad explanation no doubt.

Cheers

Dave

 

[brettdj]

RegExpFind does return all matches. Use it as an array formula. For example if F15 contains 9809abc123, then select G15:H15 and array-enter =RegExpFind(F15,"(\d+)+") to get both sets of numbers.

Thats a clever way of returning the matches discretely, I understand now why you created the array of matches

My comment was aimed more so at being able to return the entire collection of matches (ie 9809123) or any particular match (either 9809 or 123) in a single cell.

Have you tried writing a UDF to capture submatches as well?

I use a compile-time constant to switch to late-binding for released software

Can you please expand further on this?

Cheers

Dave