Deleting Specific Cells

[brightyoyo]

Hi I would like to only delete certain cells in a column that do not match a certain format. For example I have this data

[pre]

706832-02	0	REFERENCE
706831-02	1
S706831-02	0
706830-02	0	REFERENCE
C0603C102K5RACT	1	C46
C0603C104K3RAC	1	C31
C0805N101K201N	3	C19,32,33
0805N221K201NXT	1	C11
501R18W103KV4E	3	C9,14,23
12061C104KAT2A	11	C1,6,10,12,13,24,27,34,35,49,51
9-S24041675LLL	10	E4,9,10,12,26,31,32,33,43,48
9-S16241670LLL	12	E1,2,7,8,23,24,29,30,40,41,46,47
SN30PB70	0	QTY AS REQ'D
SN63PB37	0	QTY AS REQ'D
704854-01	1	L10
#128 EPOXY	0	QTY AS REQ'D
701825-01	0	QTY AS REQ'D
7019834	        0	QTY AS REQ'D
145	        0	QTY AS REQ'D
1B31	        0	QTY AS REQ'D
QCP-1018	0	REFERENCE
TPSE336K035R010	4	C25,26,29,30
ERJ-M1WSF10MU	1	R15
and I want to delete the row that does not match the letter number followed by a comma format. The result I want is:

C0603C102K5RACT	1	C46
C0603C104K3RAC	1	C31
C0805N101K201N	3	C19,32,33
0805N221K201NXT	1	C11
501R18W103KV4E	3	C9,14,23
12061C104KAT2A	11	C1,6,10,12,13,24,27,34,35,49,51
9-S24041675LLL	10	E4,9,10,12,26,31,32,33,43,48
9-S16241670LLL	12	E1,2,7,8,23,24,29,30,40,41,46,47
704854-01	1	L10
TPSE336K035R010	4	C25,26,29,30
ERJ-M1WSF10MU	1	R15

[/pre]
Thank You


Edit by Luke M

Table formatting corrected (added backticks `) to preserve column alignment.

 

[koi]

let say your example start from A1, so in B1 put this =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",2))+2,1)+0 and copy down


then you will see #value and some numbers.


now you need to applied filter on column A and B, select B and choose only #values


then press F5 > specials, select visible cells only, right click on one of the cell there and click delete


hope this is help

 

[brightyoyo]

Hi, Sorry the format did not show the way I intended it to, so I will link an example of my data and the results I would like.


https://www.dropbox.com/sh/rhffk9zwqy0b6yw/TP-BfxVSic

 

[Luke M]

Modifying koi's idea, I'd create a helper column with this formula:

=AND(LEN(A2)>=2,ISNUMBER(1*MID(A2,2,1)))

Then filter the helper column for FALSE and delete those rows.

 

[brightyoyo]

Luke M it worked, except for a small portion of the list. Can you look at my file?


https://www.dropbox.com/sh/rhffk9zwqy0b6yw/TP-BfxVSic

 

[Luke M]

In SirJB7's words, I suffer from CASFFML (can't access files from my location). Can you post a sample of what the portion looks like? Sorry.

 

[Faseeh]

Hi brightyoyo,


i think you are talking about this part:

[pre]

CAPN/U	C21
42CTQ030S	CR1
40CTQ045S	CR5
43CTQ100S	CR12
MURS320T3	CR15,16

[/pre]

??


Regards,

 

[Faseeh]

@ brightyoyo


Please try this one, that adds one more criteria to the Luke's formula:

=AND(LEN(B65)>=2,IF(ISNUMBER(1*MID(B65,2,1))=TRUE,ISNUMBER(1*MID(B65,2,1)),ISNUMBER(1*MID(B65,3,1))))

Regards,

 

[SirJB7]

Hi, brightyoyo!

Still making things not so easy for all us?

http://chandoo.org/forums/topic/extracting-and-converting-data#post-100699

And now the files are three, I don't want see your next link, maybe 1234 files?

Regards!

 

[brightyoyo]

Faseeh that worked. Thank you all once again.