Re: Deleting garbage

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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" <diy@xxxxxxxxxxxxxxx> wrote in message
> news:OzRtuZPUFHA.3840@xxxxxxxxxxxxxxxxxxxxxxx
> > 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" <diy@xxxxxxxxxxxxxxx> wrote in message
> > news:Owgu5SPUFHA.752@xxxxxxxxxxxxxxxxxxxxxxx
> > > It just gives me a blank.
> > >
> > >
> > > "Ken Wright" <ken.wright@xxxxxxxxxxxxxxxxxx> wrote in message
> > > news:ecI92MPUFHA.1148@xxxxxxxxxxxxxxxxxxxxxxx
> > > > 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" <ab@xxxxxxxxxxxxxxx> wrote in message
> > > > news:O5sOa4OUFHA.2616@xxxxxxxxxxxxxxxxxxxxxxx
> > > > > 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.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
.

Relevant Pages

  • Re: Array in javascript
    ... God damn it was an after-midnight typo, ... it will error-out just as quickly with any value type ... in the inner arrays. ... components are logically incapable of exhibiting that quality at all. ...
    (comp.lang.javascript)
  • Re: display correct toolbar icon on starting excel application program
    ... typo. ... keepITcool ... ilyaskazi wrote: ...
    (microsoft.public.excel.programming)
  • Re: The next number that is not in an array
    ... On Thu, Jul 17, 2008 at 9:05 PM, Michael W. Ryder ... I think there is a typo in here;) ... e.g. very densely populated rn arrays ...
    (comp.lang.ruby)
  • Re: Newbie help needed with Applet
    ... I strongly suggest you get comfortable with arrays then, ... typo could really cost you a lot of debugging time. ...
    (comp.lang.java.programmer)
  • Ruby documentation typo
    ... I just stumbled upon a typo in the Ruby documentation and thought it ... Sometimes creating arrays of words can be a pain, ... Prev by Date: ...
    (comp.lang.ruby)