Forum Discussion

KAM_Mumin's avatar
KAM_Mumin
Brass Contributor
Sep 15, 2022
Solved

MS Excel First Name/Last Name Formula

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

 

 

developer
excel
Formulas and Functions
Macros and VBA
Office Scripts

4 Replies

  • Nothing_Left_to_Lose's avatar
    Nothing_Left_to_Lose
    Brass Contributor
    Sep 15, 2022

    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

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Sep 15, 2022

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

     

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

     

     

    • Harun24HR's avatar
      Harun24HR
      Bronze Contributor
      Sep 15, 2022

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

       

Resources