Hello Experts, I need help please, I need a formula to search a number in a sentence. Example: In row 1, I will search a number that is greater than 50 (50 can be change anytime by the user from 1 to 300), so the answer is 150, 100, 75 & 201. The formula will be applicable from row 1 to row 5. In row 4, the answer is 135, 160 & 165. Thank you, Carlito
Last edited by carlito.salera; 04-21-2020 at 09:36 AM.
You don't specify how the "valid" results are to be returned - i.e. concatenated string, one value per cell etc
For a concatenated string, given XL version, I would say that a formula based approach will be very complex, and a User Defined Function would be a better option, e.g.:
the above, stored in a standard module in VBE, could be called from a cell as:
Function ExtractNumber(rngNo As Range, dblMin As Double, Optional strDelim As String = " ") As String Dim av1, lngX As Long av1 = Split(rngNo(1).Value, strDelim) For lngX = LBound(av1) To UBound(av1) If Val(av1(lngX)) > dblMin Then av1(lngX) = Val(av1(lngX)) Else av1(lngX) = Empty End If Next lngX ExtractNumber = Replace(Application.Trim(Join(av1)), " ", ";") End Function
where D2 holds the threshold value which, if set to 50, would return following results:Formula:
C4:
=ExtractNumbers(B4,$D$2)
150;100;75;201
250;85;180;75
140;95;200
135;160;165
90;210
if, conversely, you want to return each number into separate cells post back - and advise how you want them returned, whether you want in any specific order etc...
Last edited by XLent; 04-19-2020 at 05:26 AM. Reason: replaced strDelim with Empty
Hello XLent, Thank you very much! The VBA work, I did additional steps to get the result I am looking for. So using the excel function will not work? Thank you, Carlito
Try this
Enter with Ctrl+Shift+Enter.=TEXTJOIN(", ",TRUE,IF(ISNUMBER(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(B4,",","")," ",REPT(" ",255)),255*ROW(INDIRECT("1:100")),255))), IF(--TRIM(MID(SUBSTITUTE(SUBSTITUTE(B4,",","")," ",REPT(" ",255)),255*ROW(INDIRECT("1:100")),255))>50, --TRIM(MID(SUBSTITUTE(SUBSTITUTE(B4,",","")," ",REPT(" ",255)),255*ROW(INDIRECT("1:100")),255)),""),""))
Hello Phuocan! Thank you very much for the help. I will try the formula. Thank you, Carlito
If, per profile, you're running 2016 I don't think you will be able to use TEXTJOIN, hence suggestion of UDF.
If you do have access to TEXTJOIN then another, slightly shorter, alternative would be:
Formula:D4:
=TEXTJOIN(";",,INDEX(TEXT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4&"@",": ",""),",","")," ","@"&REPT(" ",16)),ROW($1:$500),16),),15),"0;;;\0"),"[>"&$D$2&"]0;"""""),,0))
confirmed with Enter
where B4 holds your string, and D2 the min threshold (e.g. 50)
Hello Phuocam, The formula works based on the example I provided. I did a what-if by adding more nests, the formula show a different result. Do you have a formula/function that we don't to use the ctrl+shift+enter? I will use Excel 365. Thank you, Carlito
If you are using Excel 365. then please update your forum profile, which currently says Excel 2016 - they are NOT the same.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Hello XLent, Thank you very much for providing another formula. I will use your new formula to excel 365, I will get back to you. Thank you, Carlito
Carlito - please don't ignore post #8. Thanks.
Hello AliGW, Sorry, I am not ignoring your message, I am trying to change the version but failed. I click edit but not successful. Please let me know on how to change the version. Thank you, Carlito
Go to Settings (top right) | Edit Profile ...
Change the appropriate field and then Save (at the bottom).
Sorry, but I am not able to do it for you.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
Hello AliGW, Thank you for the information, I was able to modify. The computer I am regularly using is 2016, its another computer has the 365 which I will test the latest formula I just received earlier. I will modify the status later. Thank you, Carlito
OK - in that case, give both (see how I have done it under my name to the left here).
Hello XLent, the formula worked. Thank you very much for the HELP! Carlito
Last edited by carlito.salera; 04-23-2020 at 10:53 AM.
Another one
C4
=SUBSTITUTE(TRIM(TEXTJOIN(" ",,TEXT(RIGHT(TRIM(MID(SUBSTITUTE(B4,",",REPT(" ",200)),ROW(A$1:A$20)*200-199,200)),3),"[>300] ;[>50]0; ; ")))," ",",")
Hello Bo-Ry, Thank you for the help. I will use the formula later, I will get back to you. Thank you, Carlito
Hello Bo-Ry, Again thank you. The formula is good, it will show the number above 50, but I can't change the 50. Do you have a formula which I can change the 50 ranging from 0 to 300? Thank you, Carlito
Key 50 in D2
=SUBSTITUTE(TRIM(TEXTJOIN(" ",,TEXT(RIGHT(TRIM(MID(SUBSTITUTE(B4,",",REPT(" ",200)),ROW(A$1:A$20)*200-199,200)),3),"[>300] ;[>"&$D$2&"]0; ; ")))," ",",")
Hello Bo-Ry, Again thank you. The formula is good but if "D2" is 1 then the formula will not work anymore. D2 is variable ranging from 1 to 300 but if your formula will read negative numbers then much better please. Thank you. Carlito
@Carlito, does the formula per post#6 no longer work, or are you simply trying to test / implement an alternative ?
Hello ELent, Your formulas worked. After our chat, I closed the thread, but I received formula from another expert, and I used and commented. I did additional request also, do you have a formula that will include reading negative numbers please? Thank you
could you provide a file with some examples, to better illustrate your requirement?...do you have a formula that will include reading negative numbers please?
Hello XLent, Please see sample. In the sample, you will see the result of your 2 formulas when D2 is -10. Thank you.
Please try at F4
=SUBSTITUTE(TRIM(CONCAT(TEXT(MID(SUBSTITUTE(TRIM(CONCAT(IF(ABS(CODE(MID(B4&REPT(" ",200),ROW(A$1:A$200),1))-51)<7,MID(B4&REPT(" ",200),ROW(A$1:A$200),1)," ")))," ",REPT(" ",100)),ROW(A$1:A$50)*100-99,100),"[<"&$D$2&"] ;[<300]0; ")))," ",";")
Hi Bo_Ry,
Whilst that will work for negative value in D2, where D2 is positive you will be left with "-" in the string -- but could be handled with an outer substitute.
I applied the same to the minor adaptation of post #6 -- changes in red.
=SUBSTITUTE(TEXTJOIN(";",,INDEX(TEXT(TEXT(LEFT(TEXT(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B4&"@",": ",""),",","")," ","@"&REPT(" ",16)),ROW($1:$500),16),),15),"0;-0;;"),"[>"&$D$2&"]0;"""""),0)),"-;","")
Carlito, re: UDF from post #2, modify first If
If Val(av1(lngX)) > dblMin And IsNumeric(av1(lngX)) Then
Xlent, Thank you for point out.
New one
=TEXTJOIN(";",,TEXT(RIGHT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(B4,",",REPT(" ",200)),ROW(A$1:A$20)*200-199,200))," "," "),4),"[>"&$D$2&"]0;"))
Hello Bo_Ry and Elent! Thank you very much for the help, highly appreciated!
Hello XLent! My excel file I used the formula is slow and I need to hit "Enter" key twice, do you know why? Thank you
Not without more info. tbh
What I would say is that the formula, in comparison to the UDF (post #2 + adaptation in post #26), will perform very poorly over large ranges.
for ex. on my machine - running the formula over 10k cells takes ~30 secs, and ~0.5 sec via UDF.
Hello XLent, I am using now the VBA and it is much better. My spreadsheet have 6000 rows and 30 columns. Do you have a formula that will read better? Thank you!
Bo_Ry's solution in post #27 is much faster than mine (post #26), so you should definitely try that
note: I still think you would still need to append the outer SUBSTITUTE (per post #26) - but this will have little impact on overhead.
regardless, for 180k cells I'd probably be erring on side of UDF, myself.
Another option for large Data is Power Query.
1. assign range name to D2 => Min
2. Create table from B3:B8
Open Power Query/Get and Transform. Click on New Query.
Open blank query in the editor, launch Advanced Editor and paste in the following code.
When data change, you need to press Ctrl+Alt+F5 or right click at Result and refresh to update result.let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Table.DuplicateColumn(Source, "B", "X"), {{"X", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "X"), #"Extracted Text After Delimiter" = Table.TransformColumns(#"Split Column by Delimiter", {{"X", each Number.From(Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}))}}), #"Filtered Rows" = Table.SelectRows(#"Extracted Text After Delimiter", each [X] >= Excel.CurrentWorkbook(){[Name="Min"]}[Content][Column1]{0} and [X] <= 300), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"B"}, {{"Result", each Text.Combine(List.Transform([X],each Text.From(_)),";")}})[[Result]] in #"Grouped Rows"
Hello Bo_Ry, I will try your formula. Thank you very much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks