Note your formula has an error! the last bb s/b aa
Also:Search accepts arrays.. so that can be shorter...:

=MID(A9,MIN(IF(ISERROR(SEARCH({"aa???? ";"bb???? "},A9)),1024,
SEARCH({"aa???? ";"bb???? "},A9))),6)

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Donna Yawanna wrote :

I made a few changes to get it to work but have been a typo on my
part. Thank you to all!!!!!!

=IF(ISERROR(SEARCH("aa???? ",A9)),IF(ISERROR(SEARCH("bb????
",A9)),"",MID(A9,SEARCH("bb???? ",A9),6)),MID(A9,SEARCH("bb????
",A9),6))




"Donna Yawanna" wrote in message
...
This was looking for 6 positions and then a blank space. I took out
the blank space and it gives me #VALUE! I also changed one of the
bb's to an aa - think that was just a typo.

Don't know why it gives me the #VALUE!

"Donna Yawanna" wrote in message
...
It just gives me a blank.


"Ken Wright" wrote in message
...
Assuming your data is in Col A starting A1, in say D1

=IF(ISERROR(SEARCH("aa???? ",A1)),IF(ISERROR(SEARCH("bb????
",A1)),"",MID(A1,SEARCH("bb???? ",A1),6)),MID(A1,SEARCH("aa????
",A1),6))

and copy down.

Then copy Col D and paste special as values and then delete Col
A.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03



--------------------------------------------------------------------
------
--
It's easier to beg forgiveness than ask
permission
:-)


--------------------------------------------------------------------
------
--

"Sandra" wrote in message
...
I have rows that look like this:

junkjunk junk more junk aa1234 still more junk
and
even
more
junk
this is also junk aa4567
and
so
is
this, just junk
junk aabcde junk junk junk junk

And I want to remove all text and spaces except the 6
character
string
that
starts with aa.
How can I do that? I've looked at Left and Right and other
functions
but
I
think I'm just too dense.