| Home |
| Search |
| Today's Posts |
|
#10
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. |
| 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 | |||
Threaded Mode
