Removing Unwanted Data

G

Gabmccormick

How can I remove unwanted data in a cell and leave only the neede
information? For example below, I only need to keep the last line A
and the data behiind it. I want to get rid of everthing else and not b
doig it manually. There is an entire column and like 15K lines wit
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)::
 
C

Claus Busch

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)::"
Click to expand...

your strings in column A
Then in B1:
=MID(A1,FIND("AJ::",A1),99)

Regards
Claus Busch
 
S

shanermuls

Gabmccormick;1611037 said:
How can I remove unwanted data in a cell and leave only the neede
information? For example below, I only need to keep the last line A
and the data behiind it. I want to get rid of everthing else and not b
doig it manually. There is an entire column and like 15K lines wit
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)::"
Click to expand...

Possible option is to create a condition in the next column which wil
create an error

For example, =if(left(xxx, 2) = "AJ", 1, 1/0) --> this will give
div/0! error where this is not equal to AJ. Then highlight the colum
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 delet
(over on left) and then 'delete entire row
 
Top