Splitting a field where letters start and numbers follow

[kherm]

In my conversion project I must make sense out of uncontrolled course coding. One field is going to become two. There are many variations but the major ones would be like this (X's mean letters and 0's mean numbers):

XXX000
XXX 000
XXXX000
XXXX 000
XX00
XX 00
XX0000

etc.

I believe there are only a handful of original fields that don't start with letters and end with numbers out of the 5000 records (I wouldn't mind hand fixing those).

I've been hunting around here for awhile, but I'm not finding anything that would split the original field based on whether the data was text or number. It doesn't matter to me about the space in the middle because the one field will be going into two fields in the new database.

Thanks in advance!

Ken

 

[RichO]

You need to call 2 functions from your query, one to return the left side of the field (GetLetters) and one to return the right side (GetNumbers).

This will also remove any space between letters & numbers.

You probably want to add 2 new fields to your table (we'll refer to them as LetterField and NumberField, but name them as you like)

Design an update query for this table. Select the 2 new fields and for the LetterField "Update To" box enter GetLetters([LetterField]) and GetNumbers([NumberField]) for the number field.

Public Function GetLetters(strCode As String) As String
    Dim i As Integer
    For i = 1 To Len(strCode)
    If IsNumeric(Mid$(strCode, i, 1)) Then
        GetLetters = Left$(strCode, i - 1)
        Exit For
    End If
    Next i
End Function

Public Function GetNumbers(strCode As String) As String
    Dim i As Integer
    For i = 1 To Len(strCode)
    If IsNumeric(Mid$(strCode, i, 1)) Then
        GetNumbers = Mid$(strCode, i)
        Exit For
    End If
    Next i
End Function

 

[kherm]

Thanks very much, RichO. I put the function calls in a query which I can either put in a static table or just join when I'm doing the final convert.

So the "for - exit for" loop will execute in full on the input field?! That suggests other things for me.

Thanks again!

Ken

You need to call 2 functions from your query, one to return the left side of the field (GetLetters) and one to return the right side (GetNumbers).

This will also remove any space between letters & numbers.

You probably want to add 2 new fields to your table (we'll refer to them as LetterField and NumberField, but name them as you like)

Design an update query for this table. Select the 2 new fields and for the LetterField "Update To" box enter GetLetters([LetterField]) and GetNumbers([NumberField]) for the number field.

Public Function GetLetters(strCode As String) As String
    Dim i As Integer
    For i = 1 To Len(strCode)
    If IsNumeric(Mid$(strCode, i, 1)) Then
        GetLetters = Left$(strCode, i - 1)
        Exit For
    End If
    Next i
End Function

Public Function GetNumbers(strCode As String) As String
    Dim i As Integer
    For i = 1 To Len(strCode)
    If IsNumeric(Mid$(strCode, i, 1)) Then
        GetNumbers = Mid$(strCode, i)
        Exit For
    End If
    Next i
End Function