Seperate Text and Numbers 1

[jcw5107]

I have searched & searched - to no avail...!!
I can't believe that my situation is different...!!
Anyhow..
I have a field in a table that I need to split up & seperate the numbers and text.
Example data:
9A/R
8 FT
8 AR
16ozA/R
What I'm after - in seperate fields:
9 A/R
8 FT
8 AR
16 ozA/R

Any suggestions or examples...??
Thanks in advance...!!
jcw5107

 

[SkipVought]

Hi,

I'd write a user defined function and use the function in my query. Past this in a Module...

Function GetValue(sVal As String, NumTxt As String)
    Dim i As Integer
    For i = 1 To Len(sVal)
        Select Case Mid(sVal, i, 1)
        Case "0" To "9"
            If UCase(NumTxt) = "NUM" Then
                GetValue = GetValue & Mid(sVal, i, 1)
            End If
        Case Else
            If UCase(NumTxt) = "TXT" Then
                GetValue = GetValue & Mid(sVal, i, 1)
            End If
        End Select
    Next
End Function

So in your query...

NumVal: GetValue([YourField],"NUM")
TxtVal: GetValue([YourField],"TXT")

Skip,

_[red][/red]

 

[jcw5107]

Skip,

Thank you so much..!! That is exactly what I was lookin' for. Thanks..!!
Star for ya..!!

jcw5107