Deleting garbage

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.

Oh, and the characters listed as starting with aa could also start with bb

"Sandra" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

Have you tried Text to columns. Depending on how your data is spacesd or
delimited, you may be able to get away with this.

Data -> Text To Columns ... Follow the wizard

HTH

"Donna Yawanna" wrote:

> Oh, and the characters listed as starting with aa could also start with bb
>
> "Sandra" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
>
>
>

No - the data before and after the 6 positions I want can vary in length.


"Jim Thomlinson" <[email protected]> wrote in message
news:[email protected]...
> Have you tried Text to columns. Depending on how your data is spacesd or
> delimited, you may be able to get away with this.
>
> Data -> Text To Columns ... Follow the wizard
>
> HTH
>
> "Donna Yawanna" wrote:
>
> > Oh, and the characters listed as starting with aa could also start with
bb
> >
> > "Sandra" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> >
> >
> >

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" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
>

It just gives me a blank.


"Ken Wright" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
> >
> >
>
>

Sub StrippinJunk()
'requires a reference to (viaTools/References):
'Microsoft VBScript Regular Expressions v5.5

Dim re As New RegExp
Dim c As Range
re.Global = True
re.Ignorecase = True
'a or b (repeat exactly 2x)
'0 to 9 (repeat exactly 4x)
re.Pattern = "([ab]{2,2}[0-9]{4,4})|(.?)"

For Each c In Selection.Cells
c.Value = Trim(re.Replace(c.Value, "$1 "))
Next
End Sub




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


Sandra wrote :

> 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.

If I was smart enough to figure out what the heck this stuff was I'd be able
to understand the ISERROR solution.

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
>
>
> Sub StrippinJunk()
> 'requires a reference to (viaTools/References):
> 'Microsoft VBScript Regular Expressions v5.5
>
> Dim re As New RegExp
> Dim c As Range
> re.Global = True
> re.Ignorecase = True
> 'a or b (repeat exactly 2x)
> '0 to 9 (repeat exactly 4x)
> re.Pattern = "([ab]{2,2}[0-9]{4,4})|(.?)"
>
> For Each c In Selection.Cells
> c.Value = Trim(re.Replace(c.Value, "$1 "))
> Next
> End Sub
>
>
>
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Sandra wrote :
>
> > 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.

sorry forgot the latebound alternative
to avoid the need for references

Sub StrippinJunkLATEBOUND()
Dim c As Range
With CreateObject("VBScript.RegExp")
.Global = True
.Ignorecase = True
.Pattern = "([ab]{2,2}[0-9]{4,4})|(.?)"

For Each c In Selection.Cells
c.Value = Trim(.Replace(c.Value, "$1 "))
Next
End With
End Sub


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


keepITcool wrote :

>
>
>
> Sub StrippinJunk()
> 'requires a reference to (viaTools/References):
> 'Microsoft VBScript Regular Expressions v5.5
>
> Dim re As New RegExp
> Dim c As Range
> re.Global = True
> re.Ignorecase = True
> 'a or b (repeat exactly 2x)
> '0 to 9 (repeat exactly 4x)
> re.Pattern = "([ab]{2,2}[0-9]{4,4})|(.?)"
>
> For Each c In Selection.Cells
> c.Value = Trim(re.Replace(c.Value, "$1 "))
> Next
> End Sub
>
>
>
>
> --
> keepITcool
> > www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Sandra wrote :
>
> > 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.

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" <[email protected]> wrote in message
news:[email protected]...
> It just gives me a blank.
>
>
> "Ken Wright" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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.
> > >
> > >
> >
> >
>
>

My dear Donna

you ARE asking in the Programming NG.
so I assume you'd understand that THIS is a macro.

open VBE (alt F11)
insert module
copy/paste my code
probably better to copy the latebound alternative)
posted later

then close vbe
in excel:
select your rangr with junk
run the macro "StrippinJunkLATEBOUND"

DAH!... else goto worksheetfunction NG.
<big grin>



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


Donna Yawanna wrote :

> If I was smart enough to figure out what the heck this stuff was I'd
> be able to understand the ISERROR solution.
>
> "keepITcool" <[email protected]> wrote in message
> news:[email protected]...
> >
> >
> >
> > Sub StrippinJunk()
> > 'requires a reference to (viaTools/References):
> > 'Microsoft VBScript Regular Expressions v5.5
> >
> > Dim re As New RegExp
> > Dim c As Range
> > re.Global = True
> > re.Ignorecase = True
> > 'a or b (repeat exactly 2x)
> > '0 to 9 (repeat exactly 4x)
> > re.Pattern = "([ab]{2,2}[0-9]{4,4})|(.?)"
> >
> > For Each c In Selection.Cells
> > c.Value = Trim(re.Replace(c.Value, "$1 "))
> > Next
> > End Sub
> >
> >
> >
> >
> > --
> > keepITcool
> > > www.XLsupport.com | keepITcool chello nl | amsterdam
> >
> >
> > Sandra wrote :
> >
> > > 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.

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > It just gives me a blank.
> >
> >
> > "Ken Wright" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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.
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > It just gives me a blank.
> > >
> > >
> > > "Ken Wright" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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.
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >

A million thank yous!!!!

"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > It just gives me a blank.
> > > >
> > > >
> > > > "Ken Wright" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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.
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >