Extracting Data

  • Thread starter Thread starter klafert
  • Start date Start date
K

klafert

I have a spreadsheet with 390 rows. In one column I have data such as this:


6" MARGINATA UPC#200653

Some of the lines have UPC# and some don't. The ones that do I would like
to extract out to another column. Thanks for any help!!!
 
data>filter>autofilter>filter on column desired>custom>contains>upc
voila
or a looping macro using if INSTR
or a formula
=IF(ISERROR(SEARCH("upc",H2)),"",H2)
 
I was using the formula, which worked but I only want the UPC Code #
extracted not the whole column - thanks
 
=IF(ISERROR(SEARCH("upc",H2)),"",MID(H2,SEARCH("upc",H2),FIND("
",H2,SEARCH("upc",H2)-1)))
 
This is the formula I used and I get a Value Error

=IF(ISERROR(SEARCH("upc",b5)),"",MID(b5,SEARCH("upc",b5),FIND("
",b5,SEARCH("upc",b5)-1)))
 
If desired, send your file to my address below along with this msg and
a clear explanation of what you want and before/after examples.
 
=IF(ISERROR(SEARCH("upc",B5)),"",MID(B5,SEARCH("upc#",B5),LEN(B5)))

OR

=IF(ISERROR(SEARCH("upc",B5)),"",MID(B5,SEARCH("upc#",B5)+4,LEN(B5)))

If this post helps click Yes
 
I should explain... my formula assume the UPC number is always located at
the end of the text.
 
Back
Top