separating alpha numeric vlue

[Ron Rosenfeld]

Hi all,
how do separate alpha numeric value for example I have values like
ABC123,AB234
starts with alpha but the length may be 2 or 3 or 4. just I want alpha value
in one column and numeric in another column.

could anyone help me ?
thanks

This Macro will split "selection" into letters and digits in the adjacent
columns.

You can hard-code "selection" to a given range, or set up the range to step
through in various ways.

To enter it, <alt-F11> opens the VB Editor. Ensure your project is highlighted
in the project explorer window, then Insert/Module and paste the code below
into the window that opens.

To use it, select your range, then <alt-F8> and run the Macro.

========================================
Option Explicit
Sub reExtr()
Dim c As Range
Dim re As Object
Set re = CreateObject("vbscript.regexp")
re.Global = True
For Each c In Selection
re.Pattern = "[^A-Za-z]"
c.Offset(0, 1).Value = re.Replace(c.Value, "")
re.Pattern = "\D"
c.Offset(0, 2).Value = re.Replace(c.Value, "")
Next c
End Sub
========================================


--ron

 

[Ron Rosenfeld]

Hi,

I'm not entirely sure what you mean but if you want to do this
Col A Col B Col C
ABC123,AB234 ABC,AB 123234

then right click the sheet tab, view code paste this in and run it

Sub extractnumbers()
Dim RegExp As Object, Collection As Object, RegMatch As Object
Dim Myrange As Range, C As Range, Outstring As String
For x = 1 To 2
Set RegExp = CreateObject("vbscript.RegExp")
With RegExp
.Global = True
If x = 1 Then
.Pattern = "\D"
Else
.Pattern = "\d"
End If
End With
Set Myrange = ActiveSheet.Range("a1:a100") 'change to suit
For Each C In Myrange
Outstring = ""
Set Collection = RegExp.Execute(C.Value)
For Each RegMatch In Collection
Outstring = Outstring & RegMatch
Next
C.Offset(0, x) = Outstring
Next
Set Collection = Nothing
Set RegExp = Nothing
Set Myrange = Nothing
Next
End Sub

It will extract a1 - a100 to columns B * C


Mike

Simpler, I think, to just replace the items that don't match.

e.g.:

re.Pattern = "[^A-Za-z]"
c.Offset(0, 1).Value = re.Replace(c.Value, "")
re.Pattern = "\D"
c.Offset(0, 2).Value = re.Replace(c.Value, "")


--ron

 

[Bernd P]

Hello Rick,

I see. But please allow that I have a different opinion.

Regards,
Bernd

 

[Rick Rothstein \(MVP - VB\)]

I see. But please allow that I have a different opinion.

I do and, in thinking about it, I should have posted my macro code as a
direct response to the OP rather than as a follow-up to your message. Please
accept my apologies for not having done that.

Rick