| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
Oh, and the characters listed as starting with aa could also start with bb
"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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
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" 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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
No - the data before and after the 6 positions I want can vary in length.
"Jim Thomlinson" wrote in message ... 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" 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. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
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. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
If I was smart enough to figure out what the heck this stuff was I'd be able
to understand the ISERROR solution. :) "keepITcool" wrote in message t.com... 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. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
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. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
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" wrote in message t.com... 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. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
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. |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
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. |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
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. |
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Deleting garbage
A million thank yous!!!!
"keepITcool" wrote in message t.com... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I delete an infinite number of garbage cells to the right | Excel Discussion (Misc queries) | |||
| Excel Text Import creates garbage text | Excel Discussion (Misc queries) | |||
| Excel opening w/ garbage | Excel Discussion (Misc queries) | |||
| delete garbage from worksheet | Excel Programming | |||
| Deleting Hyphens or Dashes from multiple cells without deleting the remaining content | Excel Programming | |||
![Sandra[_6_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
