Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Extract Number from Within Text (A2K, SR3)

    Here's a fun one.

    I've put together a query that pulls daily data from a list of assets into MS Excel (E2K, SR3). Because the number of assets is so great, as is the number of days (more than 2 years), I can't simply pull a simple query into Excel using MS Query, because the query exceeds the 65K row limit. Also, I can't pull the data into one large Pivot/Crosstab, because then the number of assets exceeds Excel's 256 row limit. So, as a compromise, I've written several crosstab queries in MS Query and imported one asset per worksheet.

    Now, the assets are listed across the top of the worksheet. The problem is, they are (usually!), listed in alphabetic order, left to right. My customer is asking for me to sort them alphabetically by asset name, then by number within the name. Unfortunately, the titles are in the format XX XXXX NN, where X is a letter, and N is a number. To make things worse, sometimes the text has no standard length, and the number of digits can be from 1 to three. As if that's not enough, there may be 1 or 2 letters appended to the end of the digits. I had mostly figured out a way to parse the data to this level when I found an asset that was immediately preceeded by a letter ("E2").

    So, for data that looks like this:
    <hr>Woody 111
    Woody 112
    Woody 115ST
    Woody 113L
    Woody 114ST
    Woodys Lounge 1
    Woody L1<hr>
    How can I parse to alphabetize thus:
    <hr>Woody L1
    Woody 111
    Woody 112
    Woody 113L
    Woody 114ST
    Woody 115ST
    Woodys Lounge 1<hr>

    Thanks in advance for any and all help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Number from Within Text (A2K, SR3)

    Why should L1 be sorted before 111?

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Number from Within Text (A2K, SR3)

    Hi Tim

    Here is my idea. Use the following functions by placing them in a module then place the UDF in a query and sort by the letter function and then the number (hide these fields). I think this will work

    <pre>Option Compare Database

    Public Function GetNumber(ByVal varItem As Variant) As Variant

    Dim i As Integer

    i = 1
    Do Until i > Len(varItem)
    If IsNumeric(Mid$(varItem, i, 1)) Then
    GetNumber = Mid$(varItem, i, 5)
    Exit Do
    End If
    i = i + 1
    Loop


    End Function

    Public Function GetLetter(ByVal varItem As String) As String

    Dim i As Integer

    i = 1
    Do Until i > Len(varItem)
    If Not IsNumeric(Mid$(varItem, i, 1)) Then
    GetLetter = Mid$(varItem, i, 5)
    Exit Do
    End If
    i = i + 1
    Loop


    End Function

    </pre>

    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Number from Within Text (A2K, SR3)

    That one won't work for 2 reasons:
    1) You should have GetNumber instead of myNumber
    2) Since this function returns a string value, the query will still sort by alphanumeric order instead of numeric order, i.e. 10 before 2.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Number from Within Text (A2K, SR3)

    Assuming with Jerry that you want to look only a the number part, you can use the function from <post:=551,391>post 551,391</post:>. You can copy this function into a standard module.
    Define two calculated columns in your query:

    FirstPart: Left([Field],InStrRev([Field]," ")-1)

    and

    LastPart: GetNumber([Field])

    where Field is the name of the asset field. Sort the query on FirstPart then on LastPart.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Number from Within Text (A2K, SR3)

    1) I'll correct the typo for getnumber and mynumber...thanks

    2) Oh well <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Jerry

  7. #7
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Number from Within Text (A2K, SR3)

    <P ID="edit" class=small>(Edited by Jezza on 26-Mar-07 23:58. To change typo)</P>OK second go. To extract just the number try this <img src=/S/grin.gif border=0 alt=grin width=15 height=15>:

    Function GetNumber(ByVal strInString As String) As String
    Dim lngLen As Long, strOut As String
    Dim i As Long, strTmp As String

    lngLen = Len(strInString)
    strOut = ""
    For i = 1 To lngLen
    strTmp = Left$(strInString, 1)
    strInString = Right$(strInString, lngLen - i)

    If (Asc(strTmp) >= 48 And Asc(strTmp) <= 57) Then

    strOut = strOut & strTmp
    End If
    Next i
    GetNumber = strOut
    End Function
    Jerry

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Number from Within Text (A2K, SR3)

    You could change the return data type to Long and use

    GetNumber = Val(strOut)

    in the next to last line... <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  9. #9
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Extract Number from Within Text (A2K, SR3)

    I did in the interim period and change it to double, but it is too late now
    Jerry

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Number from Within Text (A2K, SR3)

    It's always useful/interesting to have more than one way to skin a cat.

  11. #11
    Star Lounger
    Join Date
    Jan 2001
    Location
    Littleton, Colorado, USA
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Extract Number from Within Text (A2K, SR3)

    Thanks for your quick reply, Hans and Jerry. I apologize for my late reply.

    The problem I'm having with your approach is that Access recognizes the custom function easily, but MS Query does not, so fails to import to Excel. Is there an approach that doesn't require a custom function?

    Thanks.

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Extract Number from Within Text (A2K, SR3)

    Because of the unfortunate way the asset numbers have been set up, it won't be easy to avoid using a custom function.
    You could run a create table query in Access to create a temporary table, or add the FirstPart and LastPart fields to the table and run an update query to populate them. MS Query can then use the table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •