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 v...
developer
excel
Formulas and Functions
Macros and VBA
Office Scripts
  • OliverScheurich's avatar
    OliverScheurich
    Sep 15, 2022

    KAM_Mumin 

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

    You can try this formula.

     

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