How to separate text and numbers from one field into two fields

In query designer, how can I separate text and numbers from one field into two fields.  So, for example, If the field in the table (text field), is ABC123, put ABC in one field and 123 in another field.  Note, there will not always be 3 characters in the first part and there will not always be 3 characters in the 2nd part.
SteveL13Asked:
Who is Participating?
 
aikimarkCommented:
In case anyone is interested, here is an example of a query that can do this.  In addition to the original field, [Apparatus Assigned], I added two new fields.  I then invoked this query, starting with a parameter value = 1 and increasing that value on subsequent invocations.  I knew what the data looked like, so I stopped at 7.  When I finished, the only non-blank row in my test set that hadn't been affected by this update query was a row with no numeric part, just the alpha part.  If it had been lots of rows, I could have tweaked this query or written a new one to update those rows in the manner this query did.
UPDATE Q_29080966 
SET Q_29080966.Alpha = Left([Apparatus Assigned], parmPosn - 1),
Q_29080966.Num = Mid([Apparatus Assigned], parmPosn),
[Apparatus Assigned] = Null
WHERE IsNumeric(Mid([Apparatus Assigned], parmPosn))
And [Apparatus Assigned] is not null

Open in new window

0
 
PatHartmanCommented:
If the first three are fixed, you can do it this way:

AlphaField:Left(MushedField, 3)

NumericField:Mid(MushedField, 4)

Put each of these (after changing the column names) into the field cells of a query.
0
 
SteveL13Author Commented:
They won't be fixed.  As I wrote... Note, there will not always be 3 characters in the first part and there will not always be 3 characters in the 2nd part.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
PatHartmanCommented:
Sorry, I just skipped right over the first "not".  The only other alternative is to write two functions.  One that loops through the field character by character and extracts the alpha characters and the second to extract the numeric characters.

Here's one the pulls out numbers from the FRONT of a string.  You'll need to modify it slightly to not end the loop prematurely but it will give you a start.  
Public Function PatsVal(str As Variant) As String
'''''''''''Written to replace the VB Val() function which returns undesirable results (for our purposes) when the string includes
'''''''''''a d or e followed by another number - 3d4 or 1e3.2 for example.  VBA interprets the e and d as scientific notation.
'''''''''''IsNumeric() has a similar problem but we are using it in this function to analyze only a single character
'''''''''''at a time and that works fine.

    Dim strNumbers As String
    Dim i As Integer
    Dim ValLen As Integer
    Dim EndLoop As Boolean
    
    ValLen = Len(str)
    If ValLen = 0 Then
        PatsVal = ""
        Exit Function
    End If
    
    EndLoop = False
    i = 1
    
    Do Until EndLoop = True
        If i > ValLen Then
            EndLoop = True
        Else
            If IsNumeric(Mid(str, i, 1)) Then
                strNumbers = strNumbers & Mid(str, i, 1)
                i = i + 1
            Else
                EndLoop = True
            End If
        End If
    Loop
    PatsVal = strNumbers & ""
    
End Function

Open in new window

Here is a function that finds alphabetic characters.  You would use it inside a loop similar to the above in place of the IsNumeric() function.
Function IsAlphabetic(char As String, Optional IncludeNumbers As Boolean) As Boolean
    If Len(char) <> 1 Then      'check length
        IsAlphabetic = False
        Exit Function
    End If
    If (Asc(char) >= 65 And Asc(char) <= 90) Or (Asc(char) >= 97 And Asc(char) <= 122) Then
        IsAlphabetic = True
    Else
        If IncludeNumbers = True Then
            If Asc(char) >= 48 And Asc(char) <= 57 Then  '0-9
                IsAlphabetic = True
            Else
                IsAlphabetic = False
            End If
        Else
            IsAlphabetic = False
        End If
    End If
End Function

Open in new window

0
 
aikimarkCommented:
You can invoke the following function from a formula or VBA routine.  The second parameter, parmPart, determines which part is returned.  The parmPart parameter is numeric and can be 0 (zero) or 1 (one).
Execution example:
Debug.Print Q_29080966("Abc123",1)
123
Debug.Print Q_29080966("Abc123",0)
Abc
Debug.Print Q_29080966("Abc123")
Abc

Open in new window

Public Function Q_29080966(ByVal parmString, Optional ByVal parmPart = 0) As String
    Static oRE As Object
    Dim oMatches As Object
    If oRE Is Nothing Then
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = False
        oRE.Pattern = "([A-Za-z]*)(\d*)"
    End If
    If oRE.test(parmString) Then
        Set oMatches = oRE.Execute(parmString)
        Q_29080966 = oMatches(0).submatches(parmPart)  '0=alpha, 1=num
    Else
        Q_29080966 = vbNullString
    End If
End Function

Open in new window

0
 
PatHartmanCommented:
Good answer aikimark.

Steve, the real solution is to do this ONCE and actually fix the data.  Mushing multiple attributes into a single field violates first normal form which says all attributes should be atomic.  I.e. not further divisible.

Doing things like this in queries takes quite a lot of processing time.  It is far better to concatenate the two pieces for display than to attempt to separate them later.
0
 
aikimarkCommented:
This can also be done with queries at the SQL level, with or without any VBA function.
0
 
SteveL13Author Commented:
I am trying to separate the field into two fields in a query because I want to end up sorting the query results by the letter string first and the number string second.  I don't know how to apply the solutions in query designer.
0
 
SteveL13Author Commented:
Alimark, I am going to try your latest suggestion today.
0
 
aikimarkCommented:
Looks like SteveL13 has returned.  Since this thread is active, I'm canceling my close recommendation.
0
 
SteveL13Author Commented:
Alimark,  I tried your suggestion but had no luck.  I'm sure I'm doing something wrong.  Just to restate the challenge:

In query designer, how can I separate text and numbers from one field into two fields.  So, for example, If the field in the table (text field), is ABC123, put ABC in one field and 123 in another field.  Note, there will not always be 3 characters in the first part and there will not always be 3 characters in the 2nd part
0
 
aikimarkCommented:
What EXACTLY did you try?
0
 
SteveL13Author Commented:
This:

UPDATE tblCategoryInfo SET tblCategoryInfo.JobNo = Left([JobNo],parmPosn-1), tblCategoryInfo.JobNo = Mid([JobNo],parmPosn), tblCategoryInfo.JobNo = Null
WHERE (((IsNumeric(Mid([JobNo],[parmPosn])))<>False) AND ((tblCategoryInfo.[JobNo]) Is Not Null));

Open in new window


When I try to run the query I get...
parmPosm (enter parameter value)
0
 
PatHartmanCommented:
What is parmPosn?  Is it a field in tblCategoryInfor?  The Mid() function is expecting a starting position.
0
 
SteveL13Author Commented:
It came from a suggestion that aikimark made earlier.  I don't know what it means.
0
 
PatHartmanCommented:
Well, you are being prompted for it because it isn't in the table.  You might want to review ailimark's suggestion or ask for your money back :)
0
 
aikimarkCommented:
parmPosn is the place where the query will do the split if it is numeric.  You run this query, starting with a 1 value for parmPosn and increasing by 1 each iteration until you have parsed all the rows, probably the max value will be the max string length.
0
 
SteveL13Author Commented:
I don't know what to do with this information.  To repeat:

In query designer, how can I separate text and numbers from one field into two fields.  So, for example, If the field in the table (text field), is ABC123, put ABC in one field and 123 in another field.  Note, there will not always be 3 characters in the first part and there will not always be 3 characters in the 2nd part.
0
 
aikimarkCommented:
@Steve
in query designer
You've been offered several different solutions.  If your solution constraints include "doing this in the query designer", then please make that clear.  We experts have offered you several solutions.  If you don't understand them or are having problems with them, then this question thread is the place to have that discussion.

All of these solutions involve either code or queries.  Even if you had an expression that would do the parsing, the query would need to be executed, which is NOT in the query designer.
0
 
SteveL13Author Commented:
I'm sorry for any confusion I may have caused.  Right from the start I said...  "In query designer....".  I still don't know how to use what's been provided.  So please see attached.  Maybe it will help.  I need, in query designer, to split the alpha from the numeric in JobNumber.
Example.accdb
0
 
PatHartmanCommented:
Steve, your requirements are sufficiently complicated that you need to use VBA.  Several people including myself gave you code samples.  You need to create a FUNCTION in a standard module - NOT a form or report or class module - using one of the examples.  Then you would call the function in your query.  I believe I suggested two separate functions because it is less confusing to do one and only one thing in a procedure.   Plus it is better practice since it follows the principles of coupling and cohesion.  Just because you are working with the same field, doesn't mean you should use a single procedure.  Then you would use them this way in your query

Select GetAlpha(yourfield) as Alpha, Getnum(yourfield) As Num, fld2, fld3, fld4, ....
From YourTable
0
 
aikimarkCommented:
@Pat
your requirements are sufficiently complicated that you need to use VBA
I disagree with this assertion.  The query I posted will accomplish this when invoked with the increasing parameter values I've described.
0
 
PatHartmanCommented:
The problem aikimark is you need something to control the loop.  I didn't see your original suggestion so I don't know how you told Steve to create the loop.  Since we have a new thread, please either post it again or post a link.
0
 
aikimarkCommented:
No automation required.  Just invoke the query manually. (from the query designer if that makes him comfortable)
0
 
PatHartmanCommented:
parmPosn is the place where the query will do the split if it is numeric.  You run this query, starting with a 1 value for parmPosn and increasing by 1 each iteration until you have parsed all the rows, probably the max value will be the max string length.
We are out of context since I don't recall your original answer.  What exactly does "increasing by 1 each iteration" mean?  Are you saying the expression you posted does this or are you saying that Steve has to call the query manually a dozen times if the field to parse is 12 characters long.

Extracting letters and numbers from a string is easy with integral VBA functions IF:
1. One (or both) of the parts of the string is fixed in length.  For example - always starts with three letters
OR
2. The two parts are separated by a delimiter

IF neither of those is true, then you need a loop to examine the field character by character and pull out all the letters or all the numbers one at a time.  That requires VBA since SQL has no looping construct.
0
 
aikimarkCommented:
The former.  Invoke the query the number of times equal to the max length of any string in the column.  Increment the numeric value you give for the parmPosn with each invocation, starting with 1 for the first iteration.

Note: It is likely that the maximum possible number of iterations will be less than the max string length.
0
 
aikimarkCommented:
Although it may be possible to use a TallyTable to supply the values, it isn't something that I tried.
https://www.experts-exchange.com/articles/5410/Creating-and-Using-a-Tally-Table-in-Access.html
0
 
PatHartmanCommented:
aikimark, I hate to keep up with this but you have not explained how you expect to do this with no VBA.

Steve,
If you close this thread and go back to the one that had function solutions, pick one and see if you can get it to work based on my explanation of how to use it in the query.  Ask your questions in the other thread.  This deviation has not been productive.
0
 
aikimarkCommented:
Given the string examples we have seen, I invoked the query in my test database against similar strings.  I only needed to invoke the query 6-7 times before all the strings had been split.

This seems like a one-shot problem.  Sometimes, full automation (ala VBA or macro) isn't necessary.
0
 
PatHartmanCommented:
The problem is on going unless the table is updated to split the values.  I suggested that in my original comment and Steve never responded.
0
 
SteveL13Author Commented:
Pat, I think you are referring to ID 42450254.  If so, I am sorry I don't know what to do with the functions.  How do I fix the table using the suggestions?
0
 
PatHartmanCommented:
In an earlier post in this thread, I gave an example of using a function in a query which is what you seem to need.

Select GetAlpha(yourfield) as Alpha, Getnum(yourfield) As Num, fld2, fld3, fld4, ....
From YourTable

But first you need to get the function built and working.  I know there are at least two code suggestions.  Mine was not specific to your situation and so would need modification but I think someone built code for you.  Did you ever try it?

You might want to hire someone using a GIG to set this up for you. I don't do gigs but some of the experts do.
0
 
SteveL13Author Commented:
I am requesting that this topic be closed simply because I just don't know how to get the job done in spite of all of the suggestions.  Maybe another day.
0
 
aikimarkCommented:
@Steve

Did you run my query and supply a 1 when prompted for parmPosn?
0
 
SteveL13Author Commented:
I am requesting that this topic be closed simply because I fail to understand the solutions.
0
 
aikimarkCommented:
@Steve
...I fail to understand the solutions
Since you ran my query once, did you follow my instructions about running it with a 1 response, then a 2 response, then a 3 response, then a 4 response?  If not, why.  I know that you have successfully created a query and that you know how to run queries.  What part of my instructions don't you understand?

When you wen't missing during some period of this question thread, I recommended a solution set.  It is best if you accept the posted solutions even if you don't understand them.  Other readers of this thread will benefit.
https://www.experts-exchange.com/questions/29080966/How-to-separate-text-and-numbers-from-one-field-into-two-fields.html#a42468829
0
 
aikimarkCommented:
solutions provided
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.