Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Removing Unwanted Data
How can I remove unwanted data in a cell and leave only the needed information? For example below, I only need to keep the last line AJ and the data behiind it. I want to get rid of everthing else and not by doig it manually. There is an entire column and like 15K lines with cells like this.
"NOTE CODE::NOTE DESC::NOTE VALUE::TOLERANCE SP::SPECIAL: SHIPPED WITH ROD COLLAPSED SP::SPECIAL: SHIPPED WITH NUT THREADED ON ROD AG::FINISH SPECS: AS PURCHASED AA::NUMBER STAMPED ON UNIT::752802:: SP::SPECIAL:::Gabriel Equivalent Kit 141682:: SP::SPECIAL:::ADD Part No. 474774:: FF::FREE FORM NOTE::2000-01 Nissan Maxima/Infiniti I30/I35 FL:: AJ::PRODUCT WEIGHT::12.8 lbs [/b](5.8 kgs)::" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Removing Unwanted Data
Hi,
Am Mon, 8 Apr 2013 17:09:54 +0000 schrieb Gabmccormick: How can I remove unwanted data in a cell and leave only the needed information? For example below, I only need to keep the last line AJ and the data behiind it. I want to get rid of everthing else and not by doig it manually. There is an entire column and like 15K lines with cells like this. "NOTE CODE::NOTE DESC::NOTE VALUE::TOLERANCE SP::SPECIAL: SHIPPED WITH ROD COLLAPSED SP::SPECIAL: SHIPPED WITH NUT THREADED ON ROD AG::FINISH SPECS: AS PURCHASED AA::NUMBER STAMPED ON UNIT::752802:: SP::SPECIAL:::Gabriel Equivalent Kit 141682:: SP::SPECIAL:::ADD Part No. 474774:: FF::FREE FORM NOTE::2000-01 Nissan Maxima/Infiniti I30/I35 FL:: AJ::PRODUCT WEIGHT::12.8 lbs [/b](5.8 kgs)::" your strings in column A Then in B1: =MID(A1,FIND("AJ::",A1),99) Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
|
|||
|
|||
Quote:
For example, =if(left(xxx, 2) = "AJ", 1, 1/0) -- this will give a div/0! error where this is not equal to AJ. Then highlight the column with the formula and then Ctrl + G (this gets you to the Go to menu Then select Special Then select Formula and untick all except 'Errors' Hit ok This will highlight all cells with errors (div/0! s) Then you can (excel 2007) select home tab on the ribbon and then delete (over on left) and then 'delete entire rows |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing unwanted symbols eg | Excel Programming | |||
Removing unwanted characters | Excel Discussion (Misc queries) | |||
Removing unwanted data in a column | Excel Discussion (Misc queries) | |||
removing unwanted VBAProject | Excel Programming | |||
removing unwanted macros... | Excel Discussion (Misc queries) |