searching for a number in a string

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

searching for a number in a string

Post by bknight »

I have a bunch of entries in the form of
3/24/2023 10:20:18 SOLD -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx
3/24/2023 10:13:43 BOT +1 /ZNM23:XCBT @116'140 -0.82 -2

What I'm looking for is a function that will find the only the sign and number after the SOLD or BOT. In this case the first two would be -1 an +1. I tried a MID function looking backward in the string but that was only partially correct.

Toranaga
3StarLounger
Posts: 254
Joined: 15 Aug 2016, 11:23

Re: searching for a number in a string

Post by Toranaga »

Try:

=TRIM(MID(A1,SEARCH("sold",A1)+4,3))
=TRIM(MID(A2,SEARCH("bot",A2)+4,3))

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: searching for a number in a string

Post by HansV »

If the number could be more than 1 digit:

=IFERROR(TRIM(MID(A1, IFERROR(SEARCH("SOLD", A1), SEARCH("BOT", A1))+4, SEARCH(" ", A1, IFERROR(SEARCH("SOLD", A1), SEARCH("BOT", A1))+5)-IFERROR(SEARCH("SOLD", A1), SEARCH("BOT", A1))-4)), "")
Best wishes,
Hans

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

Re: searching for a number in a string

Post by bknight »

Never used that one and in these cases the absolute number was always less than 10. Thanks.

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

Re: searching for a number in a string

Post by bknight »

Toranaga wrote:
25 Mar 2023, 15:21
Try:

=TRIM(MID(A1,SEARCH("sold",A1)+4,3))
=TRIM(MID(A2,SEARCH("bot",A2)+4,3))
Those both worked, but needed to be included in an if statement.

Toranaga
3StarLounger
Posts: 254
Joined: 15 Aug 2016, 11:23

Re: searching for a number in a string

Post by Toranaga »

Try:

=IFERROR(TRIM(MID(A1,SEARCH("sold",A1)+LEN("SOLD"),3)),TRIM(MID(A1,SEARCH("bot",A1)+LEN("BOT"),3)))

Toranaga
3StarLounger
Posts: 254
Joined: 15 Aug 2016, 11:23

Re: searching for a number in a string

Post by Toranaga »

If your excel is 365 or 2021 you can use this formula:

=TEXTBEFORE(TEXTAFTER(A1," ",3)," ")

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: searching for a number in a string

Post by hamster »

try Power Query

Code: Select all

// Table1
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TBD = Table.TransformColumns(Source, {{"Data", each Text.BetweenDelimiters(_, " ", "/", 2, 0), type text}}),
    Trim = Table.TransformColumns(TBD,{{"Data", Text.Trim, type text}})
in
    Trim
tbd.png
You do not have the required permissions to view the files attached to this post.

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

Re: searching for a number in a string

Post by bknight »

Thanks but the version is 2007.

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: searching for a number in a string

Post by hamster »

so upgrade to minimum XL2016 :grin:

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

Re: searching for a number in a string

Post by snb »

No need to 'up'grade for such a simple need.

'Old'fashioned UDF

Code: Select all

Function F_snb(c00)
  F_snb=right(split(c00," /")(0),2)
End Function
In cell B1:
=F_snb(A1)

@hamster
How much simpler/better is your 'up'graded powerquery suggestion ?

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: searching for a number in a string

Post by SpeakEasy »

TEXTBEFORE and TEXTAFTER are not in Excel 2021

User avatar
hamster
StarLounger
Posts: 58
Joined: 10 Mar 2021, 22:57

Re: searching for a number in a string

Post by hamster »

XL2016 and up has Power Query build-in with graphical interface
vba you need create yourself or ask for help
but choice is yours of course, good luck :cheers:

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: searching for a number in a string

Post by adeel1 »

SpeakEasy wrote:
28 Mar 2023, 11:47
TEXTBEFORE and TEXTAFTER are not in Excel 2021
is excel 2021 license base version like O365 as based of new formulas you can get it after updation?

or still its compact based means there is no updation in it.

Adeel

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: searching for a number in a string

Post by SpeakEasy »

Excel 2021 has (slightly) fewer features than Excel 365 and, unlike 365, will not get any new features, as it is a one off licence rather than a subscription

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: searching for a number in a string

Post by HansV »

Microsoft 365 is a subscription service: you pay a monthly or yearly fee. In return, you regularly get new features.

Office 2021, Office 2019, Office 2016 etc. are 'perpetual license' versions: you pay once and the features are 'frozen' upon release. You only get bug fixes and security updates. So functions such as TEXTBEFORE will not be added to Excel 2021 or older versions.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: searching for a number in a string

Post by adeel1 »

thx @SpeakEasy and @HansV sir , for your information. :thumbup:

my guess was that from 2021 may microsoft standardized all future version , paid and get updated version like O365(when updation is available).

thx all.

Adeel

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:
25 Mar 2023, 14:25
What I'm looking for is a function that will find the only the sign and number after the SOLD or BOT.
Hello bknight.
Back in 1997 I wrote the utility function strSplitAt just for you.
Also for me since VBA did not have a Split function at that time.

Code: Select all

'    3/24/2023 10:20:18 SOLD -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx
'    3/24/2023 10:13:43 BOT +1 /ZNM23:XCBT @116'140 -0.82 -2
'
' https://eileenslounge.com/viewtopic.php?p=305927#p305927
'
Sub TEST()
    Dim strTest As String
    
    strTest = "3/24/2023 10:20:18 SOLD -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx"
    Call UW.strSplitAt(strTest, "SOLD")
    Debug.Assert "-1" = UW.strSplitAt(Trim(strTest), " ")
    
    strTest = "3/24/2023 10:13:43 BOT +1 /ZNM23:XCBT @116'140 -0.82 -2"
    Call UW.strSplitAt(strTest, "BOT")
    Debug.Assert "+1" = UW.strSplitAt(Trim(strTest), " ")
End Sub
strSplitAt is super-useful for parsing strings, especially those that have a variable structure in terms of content.
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 »

HansV wrote:
29 Mar 2023, 10:54
Microsoft 365 is a subscription service: you pay a monthly or yearly fee. In return, you regularly get new features.

Office 2021, Office 2019, Office 2016 etc. are 'perpetual license' versions: you pay once and the features are 'frozen' upon release. You only get bug fixes and security updates. So functions such as TEXTBEFORE will not be added to Excel 2021 or older versions.
I'm not paying any monthly fees. As a matter of fact I would still be using Office 97 but a job requirement required shifting to Office 2007
Last edited by bknight on 29 Mar 2023, 18:45, edited 2 times in total.

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, 11:35
bknight wrote:
25 Mar 2023, 14:25
What I'm looking for is a function that will find the only the sign and number after the SOLD or BOT.
Hello bknight.
Back in 1997 I wrote the utility function strSplitAt just for you.
Also for me since VBA did not have a Split function at that time.

Code: Select all

'    3/24/2023 10:20:18 SOLD -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx
'    3/24/2023 10:13:43 BOT +1 /ZNM23:XCBT @116'140 -0.82 -2
'
' https://eileenslounge.com/viewtopic.php?p=305927#p305927
'
Sub TEST()
    Dim strTest As String
    
    strTest = "3/24/2023 10:20:18 SOLD -1 /ZNM23:XCBT @116'085 -0.82 -2 xxxx"
    Call UW.strSplitAt(strTest, "SOLD")
    Debug.Assert "-1" = UW.strSplitAt(Trim(strTest), " ")
    
    strTest = "3/24/2023 10:13:43 BOT +1 /ZNM23:XCBT @116'140 -0.82 -2".  In Access I use an I
    Call UW.strSplitAt(strTest, "BOT")
    Debug.Assert "+1" = UW.strSplitAt(Trim(strTest), " ")
End Sub
strSplitAt is super-useful for parsing strings, especially those that have a variable structure in terms of content.
Cheers, Chris
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 Excell to pick out a number in a string and place that number in a separate column, formula.