|
Extracting Data
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!!! |
Extracting Data
datafilterautofilterfilter on column desiredcustomcontainsupc
voila or a looping macro using if INSTR or a formula =IF(ISERROR(SEARCH("upc",H2)),"",H2) -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... 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!!! |
Extracting Data
I am sorry guess I wasn't completely clear, I just want to extract the UPC
Code Number. "Don Guillett" wrote: datafilterautofilterfilter on column desiredcustomcontainsupc voila or a looping macro using if INSTR or a formula =IF(ISERROR(SEARCH("upc",H2)),"",H2) -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... 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!!! |
Extracting Data
I was using the formula, which worked but I only want the UPC Code #
extracted not the whole column - thanks "Don Guillett" wrote: datafilterautofilterfilter on column desiredcustomcontainsupc voila or a looping macro using if INSTR or a formula =IF(ISERROR(SEARCH("upc",H2)),"",H2) -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... 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!!! |
Extracting Data
This is the formula I used and I get a Value Error
=IF(ISERROR(SEARCH("upc",b5)),"",MID(b5,SEARCH("up c",b5),FIND(" ",b5,SEARCH("upc",b5)-1))) "Don Guillett" wrote: =IF(ISERROR(SEARCH("upc",H2)),"",MID(H2,SEARCH("up c",H2),FIND(" ",H2,SEARCH("upc",H2)-1))) -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... I was using the formula, which worked but I only want the UPC Code # extracted not the whole column - thanks "Don Guillett" wrote: datafilterautofilterfilter on column desiredcustomcontainsupc voila or a looping macro using if INSTR or a formula =IF(ISERROR(SEARCH("upc",H2)),"",H2) -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... 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!!! |
Extracting Data
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. -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... This is the formula I used and I get a Value Error =IF(ISERROR(SEARCH("upc",b5)),"",MID(b5,SEARCH("up c",b5),FIND(" ",b5,SEARCH("upc",b5)-1))) "Don Guillett" wrote: =IF(ISERROR(SEARCH("upc",H2)),"",MID(H2,SEARCH("up c",H2),FIND(" ",H2,SEARCH("upc",H2)-1))) -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... I was using the formula, which worked but I only want the UPC Code # extracted not the whole column - thanks "Don Guillett" wrote: datafilterautofilterfilter on column desiredcustomcontainsupc voila or a looping macro using if INSTR or a formula =IF(ISERROR(SEARCH("upc",H2)),"",H2) -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... 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!!! |
Extracting Data
=IF(ISERROR(SEARCH("upc",B5)),"",MID(B5,SEARCH("up c#",B5),LEN(B5)))
OR =IF(ISERROR(SEARCH("upc",B5)),"",MID(B5,SEARCH("up c#",B5)+4,LEN(B5))) If this post helps click Yes --------------- Jacob Skaria "klafert" wrote: This is the formula I used and I get a Value Error =IF(ISERROR(SEARCH("upc",b5)),"",MID(b5,SEARCH("up c",b5),FIND(" ",b5,SEARCH("upc",b5)-1))) "Don Guillett" wrote: =IF(ISERROR(SEARCH("upc",H2)),"",MID(H2,SEARCH("up c",H2),FIND(" ",H2,SEARCH("upc",H2)-1))) -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... I was using the formula, which worked but I only want the UPC Code # extracted not the whole column - thanks "Don Guillett" wrote: datafilterautofilterfilter on column desiredcustomcontainsupc voila or a looping macro using if INSTR or a formula =IF(ISERROR(SEARCH("upc",H2)),"",H2) -- Don Guillett Microsoft MVP Excel SalesAid Software "klafert" wrote in message ... 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!!! |
Extracting Data
Give this formula a try...
=MID(A1,SEARCH("UPC",A1&"UPC"),99) -- Rick (MVP - Excel) "klafert" wrote in message ... 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!!! |
Extracting Data
I should explain... my formula assume the UPC number is always located at
the end of the text. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... Give this formula a try... =MID(A1,SEARCH("UPC",A1&"UPC"),99) -- Rick (MVP - Excel) "klafert" wrote in message ... 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!!! |
| All times are GMT +1. The time now is 06:56 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com