SOLVED

MS Excel First Name/Last Name Formula

Contributor

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

kammumin_0-1663242202619.png

kammumin_1-1663242256497.png

 

 

4 Replies

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

 

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

 

Harun24HR_0-1663243398573.png

 

best response confirmed by KAM_Mumin (Contributor)
Solution

@KAM_Mumin 

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

You can try this formula.

pc.JPG 

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

 

@KAM_Mumin 

 

Nothing_Left_to_Lose_0-1663243834246.png

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