Question

Excel (not VBA): Cell contains this "Jill Tipping, 65.34695-2999-55565284000-6284" I need a combination of formulas (such as Ex. =mid or =left =right, etc to name a few) to extract the second word (Tipping) from the cell.

i have tried =MID(A2,FIND("^",SUBSTITUTE(A2," ","^",1))+1,FIND("^",SUBSTITUTE(A2," ","^",2))-FIND("^",SUBSTITUTE(A2," ","^",1))-1) but I get an error of "#value!"

Answer 1

-> it is correct, the formula to extract characters from a string in the cell.

=MID(A2,FIND("^",SUBSTITUTE(A2," ","^",1))+1,FIND("^",SUBSTITUTE(A2," ","^",2))-FIND("^",SUBSTITUTE(A2," ","^",1))-2)

-> mid() method used to extract characters from string. the syntax of mid() is :

mid(text,starting position,number of characters)

-> find() methos used to find the position of text from given text.itssyntax is shown below:

find(what has to be find,in which text)

-> substitute method substitutes the new text in place of old text.its syntax is :

substitute(text,oldtext,newtext)

-> in the given formula, substitute method replaces space character with "^" character in the text located cell A2.

->first find method ,finds the starting position required text in A2 .second find method , finds the ending position of text and 3rd find method finds again starting position.

-> To get required text ,it is needed to find number of characters , so that subtraction of ending position and starting position operation should be done.

-> finally got the required text ,i.e . Tipping . by formula

=MID(A2,FIND("^",SUBSTITUTE(A2," ","^",1))+1,FIND("^",SUBSTITUTE(A2," ","^",2))-FIND("^",SUBSTITUTE(A2," ","^",1))-2)