searching for a number in a string

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: searching for a number in a string

Post by ChrisGreaves »

bknight wrote:
29 Mar 2023, 18:35
I don't remember any function in 1997, but may have gotten some code in the late 2010's or early 2020's. I more less fixed the problem with substituting a + sign for the "'", then a simple formula =xxx+xxx/32 works. Now that has been changed as all things do. I use an Istr function in Access, but it takes on some iterations for different situations. The issue was in Excel to pick out a number in a string and place that number in a separate column, formula.
Hi bknight.
The strSplitAt function is one I wrote myself, and attached a copy to my post.
It is a string-processing function, quite independent of the VBA platform (Word, Excel, Access, PowerPoint, Outlook, Project etc)
Your original post mentioned strings to be parsed, and it will do that.
If you are working in Excel you will make use of the cell.Text as shown in the subroutine TESTInExcel in the attached module.

Code: Select all

Sub TESTInExcel()
    Dim strTest As String
    
    strTest = ActiveCell.Text
    Call strSplitAt(strTest, "SOLD")
    Debug.Assert "-1" = strSplitAt(Trim(strTest), " ")
    
End Sub
Cheers, Chris
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: searching for a number in a string

Post by bknight »

ChrisGreaves wrote:
29 Mar 2023, 18:55
bknight wrote:
29 Mar 2023, 18:35
I don't remember any function in 1997, but may have gotten some code in the late 2010's or early 2020's. I more less fixed the problem with substituting a + sign for the "'", then a simple formula =xxx+xxx/32 works. Now that has been changed as all things do. I use an Istr function in Access, but it takes on some iterations for different situations. The issue was in Excel to pick out a number in a string and place that number in a separate column, formula.
Hi bknight.
The strSplitAt function is one I wrote myself, and attached a copy to my post.
It is a string-processing function, quite independent of the VBA platform (Word, Excel, Access, PowerPoint, Outlook, Project etc)
Your original post mentioned strings to be parsed, and it will do that.
If you are working in Excel you will make use of the cell.Text as shown in the subroutine TESTInExcel in the attached module.

Code: Select all

Sub TESTInExcel()
    Dim strTest As String
    
    strTest = ActiveCell.Text
    Call strSplitAt(strTest, "SOLD")
    Debug.Assert "-1" = strSplitAt(Trim(strTest), " ")
    
End Sub
Cheers, Chris
A question or two.
1. How does one execute the code? I basically pasted a .cvs file into a worksheet. The screen shot was the first two rows of the worksheet.
I don't see a trigger anywhere. What I was attempting to do is to sum all the +1, -1 to see if they equaled zero, that was why I indicated a new column with the results. It took about an hour to painfully cross checked two work sheets to find the error I was looking for.

Brute force over execution.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: searching for a number in a string

Post by ChrisGreaves »

bknight wrote:
29 Mar 2023, 20:27
1. How does one execute the code? I basically pasted a .cvs file into a worksheet. The screen shot was the first two rows of the worksheet.
I don't see a trigger anywhere. What I was attempting to do is to sum all the +1, -1 to see if they equaled zero, that was why I indicated a new column with the results. It took about an hour to painfully cross checked two work sheets to find the error I was looking for.
The attached workbook has in the Visual Basic Editor (Tools, Macro, Visual basic editor) a user-defined function "strInExcel" which does the trick for "SOLD" and "BOT".

Within your Excel worksheet, insert a function and choose "user defined"
Book1.png
I have placed the function to the right of your two supplied data rows, and then in cell B3 asked for the SUM() of the two string results.
Cheers, Chris

Code: Select all

Function strInExcel(cl As Range) As String
    Dim strText As String
    
    strText = cl.Text
    If InStr(1, strText, "SOLD") > 0 Then
        Call strSplitAt(strText, "SOLD")
    Else
        If InStr(1, strText, "BOT") > 0 Then
            Call strSplitAt(strText, "BOT")
        Else
            MsgBox "I cannot parse " & cl.Text
        End If
    End If
''''    Debug.Assert "-1" = strSplitAt(Trim(strText), " ")
    strInExcel = strSplitAt(Trim(strText), " ")
End Function
You do not have the required permissions to view the files attached to this post.
An expensive day out: Wallet and Grimace

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: searching for a number in a string

Post by ChrisGreaves »

bknight wrote:
29 Mar 2023, 20:27
I basically pasted a .cvs file into a worksheet.
If your CSV file was well-defined in terms of delimiters, then you could just use the strSplitFunction repeatedly until you had lopped off the leading three? fields, collect the fourth? field and return that sub-string as the result,
Cheers, Chris
An expensive day out: Wallet and Grimace

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: searching for a number in a string

Post by bknight »

ChrisGreaves wrote:
29 Mar 2023, 22:45
bknight wrote:
29 Mar 2023, 20:27
I basically pasted a .cvs file into a worksheet.
If your CSV file was well-defined in terms of delimiters, then you could just use the strSplitFunction repeatedly until you had lopped off the leading three? fields, collect the fourth? field and return that sub-string as the result,
Cheers, Chris
I really don't understand how you would use your function.

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: searching for a number in a string

Post by snb »


User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: searching for a number in a string

Post by ChrisGreaves »

bknight wrote:
30 Mar 2023, 02:28
I really don't understand how you would use your function.
Hello Bnight.
I assumed that you used my latest workbook and saw that it worked - it parsed the two data strings and gave results and summed them to show that the balance is zero.
If that's what you don't understand I will walk you through it.
If instead you are referring to strSplitAt, then upload a couple of lines of data and i will apply strSplitAt to that delimited data to make a demonstration.
Cheers, Chris
An expensive day out: Wallet and Grimace

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: searching for a number in a string

Post by bknight »

No I haven't used any of your code because as I stated I don't know how to execute, what is the trigger? I don't know to put this any plainer.

bknight
BronzeLounger
Posts: 1353
Joined: 08 Jul 2016, 18:53

Re: searching for a number in a string

Post by bknight »

Chris here is a worksheet without any code, please insert your code and then tell me how to execute your code. Please return with your code.
You do not have the required permissions to view the files attached to this post.

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: searching for a number in a string

Post by ChrisGreaves »

bknight wrote:
30 Mar 2023, 16:29
No I haven't used any of your code because as I stated I don't know how to execute, what is the trigger? I don't know to put this any plainer.
Were you using the Book1.xls that I attached?

Please go back and read this post
Then download the Book1.XLS that is attached to the post.
Then take a look at the function (you will need to open the Visual Basic Editor with Tools, Macros, Visual basic editor or similar)

The VBA code in the function "strInExcel" makes use of the function strSplitAt().
The function "strinExcel" is used in cells B1 and B2 of Book1.xls worksheet "Sheet1".

You don't need to do anything to trigger it. It works just as any regular excel function does.
Cheers, Chris
An expensive day out: Wallet and Grimace

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: searching for a number in a string

Post by snb »

For you:
You do not have the required permissions to view the files attached to this post.

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: searching for a number in a string

Post by DocAElstein »

I took a look at your stuff, Chris. – (Originally I was slightly confused by your “UW.” and Attribute VB_Name = "Module1" stuff. - But I figured it was some Class Object OOP codswallop to just get rid of, and your book1.xls workbook confirmed that, :)
Interesting that you have your own split thing.
I remember originally thinking when I started with Excel that if there are in-built functions then you should always use them, and so keep up to date and if they add more, then, great, use them as well and keep update and getting the latest version of Excel.
That initial idea/ opinion of mine, was, because I kind of figured that whoever makes these functions is smart and that whatever low level stuff he used makes the thing much more efficient than any high level VBA UDF I could ever come up with would be.
That way of thinking still makes the most common sense to me, but experience has taught me otherwise, and I am much more inclined to go for keeping some stable earlier version and, at least to some extent, make my own UDFs. I am not sure how or why it comes about, but it seems that the newer the stuff is, the more messed up problems seem to occur.
(But looking on the bright side, without that strange state of affairs, I would never be here! – I only stated because things were either broke or not working as good as I needed, and that I found they could.)

I think perhaps the built in Split is something I might use though, and have used a lot.
I, perhaps a bit naively, or in ignorance, thought it was a very old strings thing, and so therefore thought it would be perhaps stable and OK.
Also there is a third optional argument with Split, often overlooked, which allows you to specify how many times from the left you use the separator . That has sometime been useful: Here is an example of that using the test data being considered in this thread. (Its just intended by way of example to demonstrate the use of the 3rd argument in the VB Split function. The UDF from snb is better )

Code: Select all

 Sub TestSptUn3rdArgumant()  '
' Debug.Print SptUn3rdArgumant(ThisWorkbook.Worksheets.Item("Split").Range("A1").Value)
Debug.Print SptUn3rdArgumant("3/24/2023 10:20:18 SOLD -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx")
End Sub

Public Function SptUn3rdArgumant(ByVal strIt As String) As String
Dim arrSpt() As String: Let arrSpt() = Split(strIt, " ", 4, vbBinaryCompare) 'This will give an array of 4 elents
' example               "3/24/2023 10:20:18 SOLD -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx"
 Let SptUn3rdArgumant = arrSpt(3) '              -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx            1 dimensional array typically by default arte in base 0,  so  indexes are   0 1 2 3    So 3 will be the 4th element
 Let SptUn3rdArgumant = Left(arrSpt(3), 2) '    - 1

' Or just
 Let SptUn3rdArgumant = Left(Split(strIt, " ", 4, vbBinaryCompare)(3), 2)
End Function
' ______________________________________________-

My gut feel is that this sort of requirement is what Power Query is really good at. Unfortunately I expect it will be a few years if I ever get around to learning that. In the meantime, I am fortunate to know the likes of that Demon head character Mr hamster, ;) , who is one person I am glad to know that takes care of these things nicely.

'________________________________________________

Evaluate Range can be a nice solution as well for these things, and it’s always nice here to come in when someone else has done the hard work and done a formula, that is then easy to convert into an Evaluate Range solution: Example, this will do the complete range used as example in this Thread, by the Evaluate range way.

Code: Select all

 Sub EvaluateRangeIt() '   https://eileenslounge.com/viewtopic.php?p=306160#p306160
'          =IFERROR(TRIM(MID(A1,SEARCH("sold",A1)+LEN("SOLD"),3)),TRIM(MID(A1,SEARCH("bot",A1)+LEN("BOT"),3)))                                  https://eileenslounge.com/viewtopic.php?p=306160#p306160
Dim Ws As Worksheet: Set Ws = ThisWorkbook.Worksheets.Item("Split")
Dim vTemp As Variant
 Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(A1,SEARCH(""sold"",A1)+LEN(""SOLD""),3)),TRIM(MID(A1,SEARCH(""bot"",A1)+LEN(""BOT""),3)))")
 Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(" & Ws.Range("A1").Address & ",SEARCH(""sold""," & Ws.Range("A1").Address & ")+LEN(""SOLD""),3)),TRIM(MID(" & Ws.Range("A1").Address & ",SEARCH(""bot""," & Range("A1").Address & ")+LEN(""BOT""),3)))")
 
 Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(A1,SEARCH(""sold"",A1:A2)+LEN(""SOLD""),3)),TRIM(MID(A1:A2,SEARCH(""bot"",A1:A2)+LEN(""BOT""),3)))")
 Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(" & Ws.Range("A1:A2").Address & ",SEARCH(""sold""," & Ws.Range("A1:A2").Address & ")+LEN(""SOLD""),3)),TRIM(MID(" & Ws.Range("A1:A2").Address & ",SEARCH(""bot""," & Range("A1:A2").Address & ")+LEN(""BOT""),3)))")

Dim Rng As Range: Set Rng = Ws.Range("A1:A2")
 Let vTemp = Ws.Evaluate("=IFERROR(TRIM(MID(" & Rng.Address & ",SEARCH(""sold""," & Rng.Address & ")+LEN(""SOLD""),3)),TRIM(MID(" & Rng.Address & ",SEARCH(""bot""," & Range("A1:A2").Address & ")+LEN(""BOT""),3)))")

 Let Ws.Range("F1:F2").Value = vTemp
 Let Ws.Range("F1:F2").Value = Ws.Evaluate("=IFERROR(TRIM(MID(" & Rng.Address & ",SEARCH(""sold""," & Rng.Address & ")+LEN(""SOLD""),3)),TRIM(MID(" & Rng.Address & ",SEARCH(""bot""," & Range("A1:A2").Address & ")+LEN(""BOT""),3)))")
End Sub


Alan
You do not have the required permissions to view the files attached to this post.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
ChrisGreaves
PlutoniumLounger
Posts: 15498
Joined: 24 Jan 2010, 23:23
Location: brings.slot.perky

Re: searching for a number in a string

Post by ChrisGreaves »

DocAElstein wrote:
30 Mar 2023, 18:20
I remember originally thinking when I started with Excel that if there are in built functions then you should always use them, and so keep up to date and if they add more, then, great, use them as well and keep update and getting the latest version of Excel.
That initial idea/ opinion of mine, was, because I kind of figured that whoever makes these functions is smart and that whatever low level stuff he used makes the thing much more efficient than any high level VBA UDF I could ever come up with would be.
Hi Alan; I am glad you were confused over these issues. mainly because the issues can be in conflict.
Forget about any specific language.

(1) Built-in functions should be used, they execute quickly. Agreed. But when I started using VBA there was NO Split(), just as (in our On Error discussion), there was no built-in facility for blnFileExists().

So programmers write their own functions.

Programmers have been trained to do this since 1967 at least. :grin: Daniel D. McCracken covers "Arithmetic Functions" in his book on FORTRAN II programming. An Arithmetic Function in FORTRAN II is structured the same way my strInExccel() function is structured in VBA97/2000/2002/2003/2007/2010/365/366/367 etc. See also Autocoder Macros in 1955 ("The first Autocoders appear to have been the earliest assemblers to provide a macro facility.")

(2) But what do you do when you have developed a library of functions for Word97, and another library of functions for Excel97, and a common library of string, integer, long functions that need no parent application. My strSplitAt() is an example of such a function.
Now you have 37 client's application suites using these libraries. Your clients are happy; applications are humming along cutting DAYS off their delivery times.
Do you call each client and ask them to pay you to substitute the new all-singing all-dancing function Split() and then re-test all their payroll suite, their inventory suite, etc?
I doubt that they will come to the party. After all "If it ain't broke, then don't fix it"

(3) And what of your future? After six years of development in '97, 2003 comes out and it has Split().
You are committed to your existing libraries (on account of your clients).
Do you now develop program code using both functions? The use of strSplitAt is heavily invested. Most of your business program code concerns parsing and manipulating strings, be they input data in CSV format or 10,000 legal documents to be converted, but as well, the code is built around the concept of PacMan-like nibbling bits of string from the end, one sub-string at a time.
Do you rewrite all that code, including utility functions in your libraries, so that instead of looping "Until strInput is empty" you loop "For lng ... Next lng"?

That would take you hundreds of man-hours to recode, re-test, all the way up to each application (I have 64 developer applications and 118 User applications(1))

There is no built-in function that can save you even one tenth the time it takes to convert to the New-fangled method that should have been there in the first place.

If you are a Romantic Dreamer you believe that should you die suddenly, someone will take over your work. For that reason you try to stay with conventional coding schemes, so you continue to use blnFileExists and strSplitAt() ...

Finally (!) FWIW strSplitAt (and its associated functions) have been running now for over a quarter of a century, and I do believe that they are bug-free and bug-proof, so they won't cost me any time at all in tracking down errors in any application :grin:

Back to bknight: The exercise was to show how one could (a) parse a line of data and (b) extract a useful datum and finally (c) SUM them to indicate if the values (+1 and -1) are balanced. This my Book1.xls does!

(1)I am, of course, lying. Probably half of these are proof-of-concept, so think one half of 182, say Ninety applications. Of couse, for the past ten years I have not been responsible for applications that I wrote for paying clients 1997-2011.

Cheers, Chris
An expensive day out: Wallet and Grimace

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: searching for a number in a string

Post by snb »

In this case even this will do:

Code: Select all

Function F_snb(c00)
    F_snb = Split(c00)(3)
End Function
Last edited by snb on 31 Mar 2023, 09:08, edited 2 times in total.

User avatar
DocAElstein
4StarLounger
Posts: 545
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

searching for something, and I still haven't found what I'm looking for

Post by DocAElstein »

This might be a bit better,

Code: Select all

Function F_snbsIt(ByVal c00 As String) As String  '                            https://eileenslounge.com/viewtopic.php?p=306175#p306175                            https://i.postimg.cc/pL3K0Vvd/Reply-With-Quote-Reveals-The-True-Question.jpg
 Let F_snbsIt = Split(Application.Trim(c00))(3)
End Function
( https://i.postimg.cc/pL3K0Vvd/Reply-Wit ... estion.jpg )
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(