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.