INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Expression to Split a String at First Number

Expression to Split a String at First Number

(OP)
Hello.

I am building a query in Access. One of the fields included in the query (Device_Name) has values such as:

EE10000-1
PRS10004-2
MTR10018
DS20000-462

My goal is to split the string to the left of the first number so that I can have a column in the query that only houses the alphabetic prefix:
EE
PRS
MTR
DS

As you can see, the number of alpha characters in the beginning of the string is not constant, and that is where I'm stuck.

Any help is greatly appreciated!

RE: Expression to Split a String at First Number

If I understand correctly, this should work for you.
IIF(IsNumeric(Mid(FieldName, 3)), Left(FieldName, 2), Left(FieldName, 3))
I hope that helps.

RE: Expression to Split a String at First Number

(OP)
Well, I believe we are close, but the results do not yield strictly alphabetic characters:

IIf(IsNumeric(Mid([DEVICE_NAME],3)),Left([DEVICE_NAME],2),Left([DEVICE_NAME],3))

Gives me:
EE1
PRS
MTR
DS2

RE: Expression to Split a String at First Number

Try trimming the device name - IIf(IsNumeric(Mid(Trim([DEVICE_NAME]),3)),Left(Trim([DEVICE_NAME]),2),Left(Trim([DEVICE_NAME]),3))

RE: Expression to Split a String at First Number

You can create a small function in a module like the following. Save the module with a name like "modStringConversions". Call the function in your query like:
Chars: GetLeftChars(DEVICE_NAME)

CODE --> vba

Public Function GetLeftChars(pvarText As Variant) As String
    Dim intPosition As Integer
    If IsNull(pvarText) Then
        GetLeftChars = ""
     Else
        For intPosition = 1 To Len(pvarText)
            If IsNumeric(Mid(pvarText, intPosition, 1)) Then
                GetLeftChars = Left(pvarText, intPosition - 1)
                Exit For
            End If
            GetLeftChars = pvarText
        Next
    End If
End Function 

Duane
Hook'D on Access
MS Access MVP

RE: Expression to Split a String at First Number

Or, if you fancy playing with regular expressions:

CODE

Private Function GetLeftChars(strText As String) As String
    With CreateObject("vbscript.regexp")
        .Pattern = "(.+?)(\d)"
        GetLeftChars = .Execute(strText)(0).SubMatches(0)
    End With
End Function 

RE: Expression to Split a String at First Number

(OP)
dhhookom and strongm - thanks much!

RE: Expression to Split a String at First Number

Sumnor, don't forget to use "Great post? Star it" link to show appreciation for the help.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

RE: Expression to Split a String at First Number

(OP)
I did earlier today - hmmm. Will look into it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close