MS Excel First Name/Last Name Formula

[KAM_Mumin]

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...

[#2]

@KAM_Mumin If you have Excel-365 then use TEXTAFTER() and TEXTBEFORE() function.

F4=TEXTBEFORE(E4," ")

G4=TEXTBEFORE(TEXTAFTER(E4," ",-2)," ")

[#3 · Accepted solution]

@KAM_Mumin 

=MID(E4,FIND("box ",E4)+4,LEN(E4)-2-(FIND("box ",E4)+4))

You can try this formula.

 

[#4]

@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]")

[#5]

@KAM_Mumin 

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