extract text between two spaces

A

anthony

how do i use the mid function to extract the "word" between the second and
third spaces or between the fourth and fifth spaces or even between the
second and fourth spaces?

thanks in advance
 
S

Stefi

=SUBSTITUTE(A1," word","")

extracts it from anywhere. Does it suit your needs?

Regards,
Stefi


„anthony†ezt írta:
 
R

Ron Rosenfeld

how do i use the mid function to extract the "word" between the second and
third spaces or between the fourth and fifth spaces or even between the
second and fourth spaces?

thanks in advance
Click to expand...

I would use a different method (regular expressions), but to use the MID
function, where SpaceNum1 is the number of the first space (e.g. 2 for second),
and SpaceNum2 is the number of the second space, you can use:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),
SpaceNum1))+1,FIND(CHAR(1),SUBSTITUTE(A1," ",
CHAR(1),SpaceNum2))-FIND(CHAR(1),SUBSTITUTE(
A1," ",CHAR(1),SpaceNum1))-1)


--ron
 
Top