Split data string into numeric and alpha characters 1

[addy]

Hi, I have a lot of data which contains strings such as X85123 or D2345B etc.

What I would like to do is split these strings into the alpha and numeric sections, so I would end up with X, 85123 and D, 2345, B

Any ideas most welcome

Many Thanks.

 

[HarleyQuinn]

I'm going to be recommnding Regular Expressions for this one.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[MajP]

Here is an idea
build a function to find where it switches to from alpha to numeric

Public Function findSwitch(theString As String, intStart As Integer) As Integer
  Dim intCount As Integer
  intCount = 1
  Do
    intCount = intCount + 1
    Loop While IsNumeric(Mid(theString, intStart, 1)) = IsNumeric(Mid(theString, intStart + intCount, 1))
  findSwitch = intCount
End Function

this example parses a string of the form alpha/numeric/alpha
example "abc123xyz"

Public Sub parseString(theString As String)
  Dim intSwitchStart As Integer
  Dim intSwitchPos As Integer
  Dim alpha1 As String
  Dim num1 As Integer
  Dim alpha2 As String
  
  intSwitchStart = intSwitchPos + 1
  intSwitchPos = findSwitch(theString, intSwitchStart)
  alpha1 = Mid(theString, 1, intSwitchPos)
  MsgBox alpha1
  
  intSwitchStart = intSwitchPos + 1
  intSwitchPos = findSwitch(theString, intSwitchStart)
  num1 = Mid(theString, intSwitchStart, intSwitchPos)
  MsgBox num1
  
  intSwitchStart = intSwitchPos + 1
  intSwitchPos = findSwitch(theString, intSwitchStart)
  alpha2 = Mid(theString, intSwitchStart, intSwitchPos)
  MsgBox alpha2
End Sub

I think you could modify this idea to build your own split function that takes any string and splits it into an array. I ran out of time, but should be relatively easy using the above function.

 

[TheAceMan1]

How are ya addy . . .

If the data in your post origination holds true, the following function should do:

[blue]Public Function SplitDat(Dat As String) As String
   Dim Pack As String, strAlpha As String, Rpl As String
   Dim curAlpha As Boolean, preAlpha As Boolean, x As Integer
   
   Pack = Dat
   
   For x = 2 To Len(Dat)
      strAlpha = Mid(Dat, x - 1, 2)
      preAlpha = IsNumeric(Mid(Dat, x - 1, 1))
      curAlpha = IsNumeric(Mid(Dat, x, 1))
      
      If preAlpha <> curAlpha Then
         Rpl = Left(strAlpha, 1) & "," & Right(strAlpha, 1)
         Pack = Replace(Pack, strAlpha, Rpl)
      End If
   Next
   
   SplitDat = Pack
   
End Function[/blue]

And a call to the function:

[blue]   Me!TextboxName = SplitDat("D22345B")[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MajP]

I probably need to look at AceMan's post more carefully to see if I am making this harder than I thought. Here is a general split function, it was not trivial.

Public Function SplitAlphaNumeric(theString As String) As Variant
  Dim aVarArray() As Variant
  Dim intSplits As Integer
  Dim intIndex As Integer
  Dim intSwitchStart As Integer
  Dim intSwitchPos As Integer
  ReDim aVarArray(countSwitches(theString))
  intSwitchStart = 1
  Do
    intSplits = intSplits + 1
    intSwitchStart = intSwitchStart + intSwitchPos
    intSwitchPos = findSwitch(theString, intSwitchStart)
    intIndex = intIndex + 1
    aVarArray(intIndex) = Mid(theString, intSwitchStart, intSwitchPos)
    MsgBox aVarArray(intIndex)
  Loop Until intSwitchStart + intSwitchPos >= Len(theString)
  SplitAlphaNumeric = aVarArray
End Function

Public Function countSwitches(theString As String) As Integer
    Dim isChrNumeric As Boolean
    Dim intcounter As Integer
    countSwitches = 1
    isChrNumeric = IsNumeric(Left(theString, 1))
    For intcounter = 2 To Len(theString)
      If Not (IsNumeric(Mid(theString, intcounter, 1)) = isChrNumeric) Then
        countSwitches = countSwitches + 1
        isChrNumeric = Not (isChrNumeric)
      End If
  Next intcounter
End Function

Public Function findSwitch(theString As String, intStart As Integer) As Integer
  Dim intCount As Integer
  intCount = 1
  If Not (IsNumeric(Mid(theString, intStart, 1)) = IsNumeric(Mid(theString, intStart + 1, 1))) Then
    intCount = 1
  Else
     Do
       intCount = intCount + 1
     Loop While IsNumeric(Mid(theString, intStart, 1)) = IsNumeric(Mid(theString, intStart + intCount, 1)) And Not ((intStart + intCount) > Len(theString))
  End If
  findSwitch = intCount
End Function

splitAlphaNumeric uses 2 other functions:

countSplits : counts how many times it goes changes from alpha to numeric.

findSplit: returns the length of the current run given a starting position. Example (1234ABCX34). Starting at position 5 would return 3 (ABC is three characters long)

Here is a test

Public Sub test(theString As String)
  Dim intcounter As Integer
  Dim x() As Variant
  x = SplitAlphaNumeric(theString)
  For intcounter = 0 To UBound(x)
    Debug.Print x(intcounter)
   Next intcounter
End Sub

and the output

test("a1abc123qwer1234")

a
1
abc
123
qwer
1234

 

[addy]

MajP

This is very close to what I need to achieve - thanks for you help so far.

Quick question (I have never really dealt with arrays before), how do I get the values from the array into a table?

So, if I have a table with my string values in, for example:

XC12456A
D123
BG1255
PAH5568B

How do I end up with a table or workable list which would be:

XC 12456 A
D 123
BG 1255
PAH 5568 B

Thanks.

 

[MajP]

Is there only three fields max? of the form

alphapartNumericpartAlphapart

field1: alpha part
field2: numeric part
field3 alpha part (optional)

The reason I ask is the answer is much easier than an unknown amount of fields which you would have to create fields in a table dynamically.

I built the function with a lot of flexibility, but the two previous post from me or AceMan would actually be simpler approaches if the answer is always of the above form. The function will handle any amount of switches from alphabetic to numeric and a string of any length.

If the above answer is true provide the name of your table, the field to split, the name of your new fields. It will make writing the code easier for you to incorporate.

 

[addy]

Unfortunately, they will not always be the same! There will be a mix, some may only have 2 parts (e.g. D1234), some may have 7 or 8.

 

[MajP]

If that is the case to have a proper db design you should create a new table. What does this information represent, and then what do you do with it?

tblMainTable (your current table)
itemID
strOldNameString (your old string)

tblRelatedInfo
itemID (a foriegn key relating back to tblMain)
strPart (a part of the string)
strPartLocation( 1 part to 8 part if this has some meaning)
strPartMeaning (see discussion)

The reason strPartLocation may have a meaning is something like a vehicle VIN number where each part has a meaning
part1 (I think country code)
part2 (manufacturer)
partx (date) etc

this would make more sens then having 8 field in your main table where some are populated and for many records some are not (non-normalized design).

 

[MajP]

Actually I may recant what I said depending on what the parts mean and how you use them. If each part of the string has a specific meaning than it may make sense to have a unique field in the main table. If they are do not then maybe a related table. Ex

tblMain
itemID
strOldNameString
..new fields
countryCode
manufacturer
dateCreated
lotNumber

 

[MajP]

Couple other questions:
1. Are we dealing with a few records or 100 of thousands? Strategy would be different
2. Is this a fix if you ran it once or do you need to rerun this after entering new data or downloading data?

The reason is if this is a very large database I would run a procedure to do this, if it is smaller I could build functions that return each part and use them in a query. The second approach would be very flexible for a small db but might bog down in a very large one.

 

[addy]

MajP - I have given you a star as your code has proved very, very helpful.

I have figured out my own way of doing this now which will achieve what I need - perhaps not as effectively or efficiently as is possible but enough to solve what I need.

Many, many thanks for your help!

 

[MajP]

Sounds good, but I fixed this up and I may add this as a FAQ to this site. You might find this useful. Here are the functions. Drop these into a module.

Option Explicit
Public Function getPartLength(theString As String, intStart As Integer) As Integer
  'Find the length of the alphabetic or numeric part
  Dim intCount As Integer
  intCount = 1
  If Not (IsNumeric(Mid(theString, intStart, 1)) = IsNumeric(Mid(theString, intStart + 1, 1))) Then
    intCount = 1
  Else
     Do
       intCount = intCount + 1
     Loop While IsNumeric(Mid(theString, intStart, 1)) = IsNumeric(Mid(theString, intStart + intCount, 1)) And Not ((intStart + intCount) > Len(theString))
  End If
  getPartLength = intCount
End Function
Public Function SplitAlphaNumeric(theString As String) As Variant
  Dim aVarArray() As Variant
  Dim intSplits As Integer
  Dim intIndex As Integer
  Dim intSwitchStart As Integer
  Dim intSwitchPos As Integer
  ReDim aVarArray(countParts(theString) - 1)
  intSwitchStart = 1
  Do
    intSplits = intSplits + 1
    intSwitchStart = intSwitchStart + intSwitchPos
    intSwitchPos = getPartLength(theString, intSwitchStart)
    aVarArray(intIndex) = Mid(theString, intSwitchStart, intSwitchPos)
    intIndex = intIndex + 1
  Loop Until intSwitchStart + intSwitchPos > Len(theString)
  SplitAlphaNumeric = aVarArray
End Function
Public Function countParts(theString As String) As Integer
    Dim isChrNumeric As Boolean
    Dim intcounter As Integer
    countParts = 1
    isChrNumeric = IsNumeric(Left(theString, 1))
    For intcounter = 2 To Len(theString)
      If Not (IsNumeric(Mid(theString, intcounter, 1)) = isChrNumeric) Then
        countParts = countParts + 1
        isChrNumeric = Not (isChrNumeric)
      End If
  Next intcounter
End Function

Public Sub test(theString As String)
  Dim intcounter As Integer
  Dim x() As Variant
  x = SplitAlphaNumeric(theString)
  For intcounter = 0 To UBound(x)
    Debug.Print x(intcounter)
   Next intcounter
End Sub

Public Function getPart(theString As String, intPartNumber As Integer) As String
  Dim aVarArray() As Variant
  aVarArray = SplitAlphaNumeric(theString)
  If Not intPartNumber > UBound(aVarArray()) + 1 Then
    getPart = aVarArray(intPartNumber - 1)
  End If
End Function

assume I have this string:
"asdsf345as67df7"
It has the following parts:
1 asdsf
2 345
3 as
4 67
5 df
6 7

If this string is in a field "fieldName" and I want to return the fourth part "67", I can use this function in a query, procedure, or calculated control as so:

getPart([fieldName],4)

second part "345" would be
getPart([fieldName],2)

The other functions could be used as so:

To return how many parts
?countParts("asdsf345as67df7")
6
To get a specific part
?getPart("asdsf345as67df7",3)
as
and to return the array
Dim x() As Variant
x = SplitAlphaNumeric(theString)