splitting alpha and numeric data in string 5

[lindercoop]

Hello -
I am working in Access 2007. I have a string field with varying alpha/numeric combinations like:

E123456
BDI14222
RA9871
LIN4420

I need to end up with 3 fields something like this:

E123456 E 123456
BDI14222 BDI 14222
RA9871 RA 9871
LIN4420 LIN 4420

Basically want to de-concatenate based on letters vs. numbers.

I appreciate any help I can get.
Thanks

 

[GSCaupling]

Hi lindercoop,

Can you clarify a few things?

1. Is there always at least one Alpha character?
2. Are the alpha characters always on the LEFT and not mixed with the numbers?
3. What is the maximum number of alpha characters you are dealing with?
4. Will the numeric portion ever have leading zeroes that you need to retain?

I may not have a solution to offer, but I suspect the answers to these questions will help whoever does.

Best of luck!
GS

[red]******^*******
[small]I[/small] [♥] [small]Love Redundancy[/small][/red]

 

[lindercoop]

Good questions!

1. Sometimes there are no alpha characters.
2. They are always on the left.
3. The maximum alpha characters is currently 3 but could change at some point and need to be adjusted.
4. The numeric portion could have preceeding zeros.

Thanks GS.

 

[fumei]

There is probably a better way, but here is a brute force method using IsNumeric to test each character.

Sub SomeSplittingUp()
Dim strIn As String
Dim strOut As String
Dim var
Dim myArray()
Dim j As Long
Dim i As Long
Dim k As Long

myArray = Array("E123456", _
   "BDI14222", "RA9871", _
   "LIN4420")


For j = 0 To UBound(myArray)
   strIn = myArray(j)
   For k = 1 To Len(strIn)
      var = IsNumeric(Right((Left(strIn, k)), 1))
      If var Then
         Exit For
      Else
         i = k
      End If
   Next
   strOut = strOut & Left(strIn, i) & vbTab & _
   Right(strIn, Len(strIn) - i) & vbCrLf
Next
MsgBox strOut
   
End Sub

Result?


E 123456
BDI 14222
RA 9871
LIN 4420

Obviously you would not need the array. I just used it in order to action all four test items. Essentially it goes through (say using BDI14222 as the example) each character (starting at the left), and test to see if it is numeric, or not. So...

BDI14222

B...IsNumeric = False, so i = 1
D...IsNumeric = False, so i = 2
I...IsNumeric = False, so i = 3
1...IsNumeric = True, so Exit For loop for this array item(BDI14222) - set as strIn.

strOut (the output string) is:

Left(strIn, i) "BDI" Left("BDI14222", 3)
vbTab (for spacing purposes)
Right(strIn, Len(strIn) - i) "14222"
Right("BDI14222", Len("BDI1422") - 3)
Right("BDI14222", 8 - 3)
Right("BDI14222", 5)...."14222"
vbCrLf (for spacing purposes)

and then on to the next item in the array.

Gerry

 

[GSCaupling]

That's impressive, Gerry.

GS

[red]******^*******
[small]I[/small] [♥] [small]Love Redundancy[/small][/red]

 

[strongm]

Here's a regular expression solution presenting the result in two different ways. The first returns a two element string array (first element contains the number, second element contains the alpha characters). The second mimics funei's solution:[blue]

Public Function AlphanumSplit1(strAlphaNum As String) As String()
    With CreateObject("vbscript.regexp") 
        .IgnoreCase = True
        .Pattern = "(\d*)([a-z]*)"
        AlphanumSplit1 = Split(.Replace(strAlphaNum, "$1 $2"), " ")
    End With
End Function

Public Function AlphanumSplit2(strAlphaNum As String) As String
    With CreateObject("vbscript.regexp") 
        .IgnoreCase = True
        .Pattern = "(\d*)([a-z]*)"
        AlphanumSplit2 = .Replace(strAlphaNum, "$1" & vbTab & "$2")
    End With
End Function

[/blue]

 

[strongm]

And just for the heck of it a 3rd version that produces the 3 fields you mention you wanted in the original post:

[blue]Public Function AlphanumSplit3(strAlphaNum As String) As String
    With CreateObject("vbscript.regexp") 're
        .IgnoreCase = True
        .Pattern = "(\d*)([a-z]*)"
        AlphanumSplit3 = strAlphaNum & vbTab & .Replace(strAlphaNum, "$1" & vbTab & "$2")
    End With
End Function[/blue]

Oops - it's late and I'm clearly half-asleep. You need to change the .Pattern in all three examples to

.Pattern = "([a-z]*)(\d*)

 

[fumei]

Which goes to show (again) that there is more than one way to skin a string.

If you need this kind of thing fairly often it is MUCH better to have the functionality as...a Function (like strongm has). This is the kind of thing Functions are for.

However...strongm's function returns ONE string of the whole thing. I.e. "BDI 14222"

Since it is more likely that you would want the parts as separate strings: "BDI" (to go someplace), "14222" (to go someplace else) - then I would set it up as separate functions, like this:

Option Explicit

Public Function AlphaPart(strAlphaNum As String) As String
    With CreateObject("vbscript.regexp")
         .IgnoreCase = True
         .Pattern = "([a-z]*)(\d*)"
         AlphaPart = _
            .Replace(strAlphaNum, "$1")
    End With
End Function

Public Function NumPart(strAlphaNum As String) As String
    With CreateObject("vbscript.regexp")
         .IgnoreCase = True
         .Pattern = "([a-z]*)(\d*)"
         NumPart = _
            .Replace(strAlphaNum, "$2")
    End With
End Function

Now you can get each part - and use them - separately. Here demonstrated as a display in a messagebox, but of course could be the parts going (individually) someplace else.

MsgBox AlphaPart("BDI14222") & vbTab & NumPart("BDI14222")

Result? "BDI 14222"



Gerry

 

[fumei]

Or, for example, in Excel (with of course the functions available):

Dim strIn As String
strIn = "BDI14222"
Worksheets("Sheet1").Range("A3") = strIn
Worksheets("Sheet1").Range("B3") = AlphaPart(strIn)
Worksheets("Sheet1").Range("C3") = NumPart(strIn)

BDI14222 BDI 14222

Since you seem to want the original value (BDI14222), and then separate values for alpha/numeric, having separate functions for alpha and numeric could be useful.


Gerry

 

[fumei]

And finally, as a Word reference...

Option Explicit

Public Function AlphaPart([b]ByVal[/b] strAlphaNum As String) As String
    With CreateObject("vbscript.regexp")
         .IgnoreCase = True
         .Pattern = "([a-z]*)(\d*)"
         AlphaPart = _
            .Replace(strAlphaNum, "$1")
    End With
End Function

Public Function NumPart([b]ByVal[/b] strAlphaNum As String) As String
    With CreateObject("vbscript.regexp")
         .IgnoreCase = True
         .Pattern = "([a-z]*)(\d*)"
         NumPart = _
            .Replace(strAlphaNum, "$2")
    End With
End Function


Sub SplittingUpIntoTable()
Dim oTable As Table
Dim myArray()
Dim j As Long

myArray = Array("E123456", _
   "BDI14222", "RA9871", _
   "LIN4420")

Set oTable = ActiveDocument.Tables(1)
On Error Resume Next

For j = 0 To UBound(myArray)
   oTable.Cell(j + 1, 1).Range.Text = myArray(j)
   oTable.Cell(j + 1, 2).Range.Text = AlphaPart(myArray(j))
   oTable.Cell(j + 1, 3).Range.Text = NumPart(myArray(j))
Next
   
End Sub

which takes the array of "E123456", "BDI14222", "RA9871", "LIN4420", and puts the original values, plus the split (alpha and numeric) parts into separate cells in a given table.

Result?

E123456 E 123456
BDI14222 BDI 14222
RA9871 RA 9871
LIN4420 LIN 4420

Note the bolding in the Functions. For this to work the parameters passed to the functions MUST be ByVal not the VBA assumed ByRef. ByRef will cause a Compile error mismatch.

Also, the above code assumes the values are going into Table 1 (made as a table object), and the table has four rows and three columns to match.

Gerry

 

[strongm]

>strongm's function returns ONE string of the

No, no, no strongm's first function (and it was first for a reason), AlphaNumSplit1, returns the data in a two element array, so you can easily pick the bit you want without having to write two functions:

So your

MsgBox AlphaPart("BDI14222") & vbTab & NumPart("BDI14222")

becomes

MsgBox AlphaNumSplit1("BDI14222")(0) & vbTab & AlphaNumSplit1("BDI14222")(1)

And your Excel example:

[blue]    Dim strIn As String
    strIn = "BDI14222"
    Worksheets("Sheet1").Range("A3") = strIn
    Worksheets("Sheet1").Range("B3") = AlphanumSplit1(strIn)(0)
    Worksheets("Sheet1").Range("C3") = AlphanumSplit1(strIn)(1)[/blue]

And the Word version could be rewritten as:

[blue]Option Explicit

Public Function AlphanumSplit1(ByVal strAlphaNum As String) As String()
    With CreateObject("vbscript.regexp")
        .IgnoreCase = True
        .Pattern = "([a-z]*)(\d*)"
        AlphanumSplit1 = Split(.Replace(strAlphaNum, "$1 $2"), " ")
    End With
End Function

Public Sub SplittingUpIntoTable()
    Dim alphanum As Variant
    Dim row As Long
    
    On Error Resume Next
    
    With ActiveDocument.Tables(1)
        For Each alphanum In Array("E123456", "BDI14222", "RA9871", "LIN4420")
           .Cell(row + 1, 1).Range.Text = alphanum
           .Cell(row + 1, 2).Range.Text = AlphanumSplit1(alphanum)(0)
           .Cell(row + 1, 3).Range.Text = AlphanumSplit1(alphanum)(1)
           row = row + 1
        Next
    End With
    
End Sub[/blue]

If you really wanted to get crazy you could add an enum:

Public Enum AlphaSplitType
AlphaBit
NumericBit
End Enum

and then you'd be able to do things like:

[blue]MsgBox AlphaNumSplit1("BDI14222")(AlphaBit) & vbTab & AlphaNumSplit1("BDI14222")(NumericBit)[/blue]

 

[fumei]

Ooops, sorry, my apologies..I was totally incorrect. I had not paid full attention to the use of Split. of course it is an two element array and can be used for that purpose.

Doh. me bad. Plus it is - in fact - better your way. as it uses one function, rather than two.

doh.

Gerry

 

[fumei]

And, strongm you deserve a star from me for persistence, accuracy, and useful clarity.

Gerry

 

[TonyJollans]

Hi Chaps,

All good stuff, of course, but there is no need for a regexp for this simple split. Using VBA functions only ..

[blue]For Each AlphaNum In Array("E123456", "BDI14222", "RA9871", "LIN4420")
    SplitPos = InStr(AlphaNum, Right(Val(StrReverse(AlphaNum)), 1))
    MsgBox AlphaNum & vbTab & Left(AlphaNum, SplitPos - 1) & vbTab & Mid(AlphaNum, SplitPos)
Next AlphaNum[/blue]

This isn't as flexible as any of the suggestions so far, of course, but it does what was asked.

This code snippet just displays the three strings; there are all sorts of ways to make a function out of it, depending on what, exactly, one wants returned.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[lindercoop]

WOW! You guys are awesome! I have been on vacation for a couple days and will try some of these solutions on my problem. I'm sure one will suit my needs and I'll respond back accordingly over the next few days.

Again, thanks so much for all the suggestions and mad brainiacs out there!

Much appreciated!
Linda

 

[SkipVought]

Tony,

Elegant! ==> [purple]*[/purple]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

Here's a solution using native Excel functions - no VBA required:

(NOTE: The following are Array Formulas and must be entered using [Ctrl]+[Shift]+[Enter], not just [Enter])

For the Alpha- portion:
[tab][COLOR=blue white]=LEFT(A2, MATCH(TRUE, ISNUMBER(VALUE(MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))), 0) - 1)[/color]

For the -Numeric portion:
[tab][COLOR=blue white]=VALUE(MID(A2, MATCH(TRUE, ISNUMBER( 1 * MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1)), 0), COUNT(1 * MID(A2, ROW(INDIRECT("1:" & LEN(A2))), 1))))[/color]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[AnotherHiggins]

Damn! I just now noticed "I am working in Access 2007" in the OP. I was, of course, thinking about Excel.

Well there's several minutes I'm not getting back.....

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Et tu Brute?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

??

The Ides of March have long since passed.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.