searching for a number in a string
-
- BronzeLounger
- Posts: 1391
- Joined: 08 Jul 2016, 18:53
searching for a number in a string
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.
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.
-
- 3StarLounger
- Posts: 256
- Joined: 15 Aug 2016, 11:23
Re: searching for a number in a string
Try:
=TRIM(MID(A1,SEARCH("sold",A1)+4,3))
=TRIM(MID(A2,SEARCH("bot",A2)+4,3))
=TRIM(MID(A1,SEARCH("sold",A1)+4,3))
=TRIM(MID(A2,SEARCH("bot",A2)+4,3))
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: searching for a number in a string
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)), "")
=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
Hans
-
- BronzeLounger
- Posts: 1391
- Joined: 08 Jul 2016, 18:53
Re: searching for a number in a string
Never used that one and in these cases the absolute number was always less than 10. Thanks.
-
- BronzeLounger
- Posts: 1391
- Joined: 08 Jul 2016, 18:53
-
- 3StarLounger
- Posts: 256
- Joined: 15 Aug 2016, 11:23
Re: searching for a number in a string
Try:
=IFERROR(TRIM(MID(A1,SEARCH("sold",A1)+LEN("SOLD"),3)),TRIM(MID(A1,SEARCH("bot",A1)+LEN("BOT"),3)))
=IFERROR(TRIM(MID(A1,SEARCH("sold",A1)+LEN("SOLD"),3)),TRIM(MID(A1,SEARCH("bot",A1)+LEN("BOT"),3)))
-
- 3StarLounger
- Posts: 256
- Joined: 15 Aug 2016, 11:23
Re: searching for a number in a string
If your excel is 365 or 2021 you can use this formula:
=TEXTBEFORE(TEXTAFTER(A1," ",3)," ")
=TEXTBEFORE(TEXTAFTER(A1," ",3)," ")
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: searching for a number in a string
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
You do not have the required permissions to view the files attached to this post.
-
- BronzeLounger
- Posts: 1391
- Joined: 08 Jul 2016, 18:53
Re: searching for a number in a string
Thanks but the version is 2007.
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
-
- 4StarLounger
- Posts: 584
- Joined: 14 Nov 2012, 16:06
Re: searching for a number in a string
No need to 'up'grade for such a simple need.
'Old'fashioned UDF
In cell B1:
=F_snb(A1)
@hamster
How much simpler/better is your 'up'graded powerquery suggestion ?
'Old'fashioned UDF
Code: Select all
Function F_snb(c00)
F_snb=right(split(c00," /")(0),2)
End Function
=F_snb(A1)
@hamster
How much simpler/better is your 'up'graded powerquery suggestion ?
-
- 4StarLounger
- Posts: 562
- Joined: 27 Jun 2021, 10:46
Re: searching for a number in a string
TEXTBEFORE and TEXTAFTER are not in Excel 2021
-
- StarLounger
- Posts: 58
- Joined: 10 Mar 2021, 22:57
Re: searching for a number in a string
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
vba you need create yourself or ask for help
but choice is yours of course, good luck
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
-
- 4StarLounger
- Posts: 562
- Joined: 27 Jun 2021, 10:46
Re: searching for a number in a string
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
-
- Administrator
- Posts: 78535
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: searching for a number in a string
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.
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
Hans
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: searching for a number in a string
thx @SpeakEasy and @HansV sir , for your information.
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
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
-
- PlutoniumLounger
- Posts: 15641
- Joined: 24 Jan 2010, 23:23
- Location: brings.slot.perky
Re: searching for a number in a string
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
Cheers, Chris
You do not have the required permissions to view the files attached to this post.
He who plants a seed, plants life.
-
- BronzeLounger
- Posts: 1391
- Joined: 08 Jul 2016, 18:53
Re: searching for a number in a string
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 2007HansV wrote: ↑29 Mar 2023, 10:54Microsoft 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.
Last edited by bknight on 29 Mar 2023, 18:45, edited 2 times in total.
-
- BronzeLounger
- Posts: 1391
- Joined: 08 Jul 2016, 18:53
Re: searching for a number in a string
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.ChrisGreaves wrote: ↑29 Mar 2023, 11:35Hello 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.strSplitAt is super-useful for parsing strings, especially those that have a variable structure in terms of content.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
Cheers, Chris