MID function

  • Thread starter Thread starter Eva Shanley
  • Start date Start date
E

Eva Shanley

I need to get the middle entry out of a cell; the middle
entry (123456) can contain a varied number of characters
as shown below:
123 123456 12
123 12345 12
The good thing is that the 3 sections are separated by
spaces, so I've used =MID(A4,FIND(CHAR(32),A4)+1,255)to
get the middle entry out, but I'm having problems trying
to combine this formula with LEFT to get rid of the " 12"
at the end. Can you help? Thanks as always!
 
Hi Eva

not sure if there's a neater formula but this one seems to work

=MID(A1,FIND(" ",A1,1)+1,ABS(FIND(" ",A1,1)-FIND(" ",A1,FIND(" ",A1,1)+1)))

cheers
JulieD
 
Hi
try:
=TRIM(MID(A1,FIND(" ",A1)+1,FIND("^",SUBSTITUTE
(A1," ","^",2))-FIND(" ",A1)-1))
 
Frank,
Thanks for the quick response. I tried the formula but I'm
getting the dialog "The formula you typed contains a
error." Any ideas?
 
Eva
I copied this working formula directly from my sheet so it
should work.
Are you using a different Excel language version or are
you using the semicolon as separator. For the latter try:
=TRIM(MID(A1;FIND(" ";A1)+1;FIND("^";SUBSTITUTE
(A1;" ";"^";2))-FIND(" ";A1)-1))
 
Frank,
I went back to the first formula you sent me and found a
space between SUBSTITUTE and(A1; that's what was throwing
this off. The formula works great. Thanks also to Julie
and Dave whose formulas also work great!
 
Back
Top