Sep 15 2022 04:46 AM
Hi Everyone!
Here I want to get the first value and the last value look like I have (462 box 0 pcs) in a cell and I want to get the first value 462 which give me the count of box and then need the value for pcs which is 0,,, and I make the first criteria true but failed to make the second criteria which is showing me the last value but I need to show there the value 0 (means the count of pcs),,,
please can any of you give me solution for this? I give a screen short below for understanding...
Sep 15 2022 05:04 AM
@KAM_Mumin If you have Excel-365 then use TEXTAFTER() and TEXTBEFORE() function.
F4=TEXTBEFORE(E4," ")
G4=TEXTBEFORE(TEXTAFTER(E4," ",-2)," ")
Sep 15 2022 05:06 AM
SolutionSep 15 2022 05:09 AM
@Harun24HR If you are not using Microsoft-365 then use FILTERXML(). See attached file.
F4=FILTERXML("<t><s>" &SUBSTITUTE(E4," ","</s><s>") & "</s></t>","//s[1]")
G4=FILTERXML("<t><s>" &SUBSTITUTE(E4," ","</s><s>") & "</s></t>","//s[position()=last()-1]")
Sep 15 2022 05:15 AM
The NumsLeft and NumsRight functions are part of the free 'Custom_Functions' Excel add-in.
(20+ excel functions that work exactly like the built-in excel functions)
Download from OneDrive...
https://1drv.ms/u/s!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU
'---
Nothing Left to Lose
Sep 15 2022 05:06 AM
Solution=MID(E4,FIND("box ",E4)+4,LEN(E4)-2-(FIND("box ",E4)+4))
You can try this formula.