| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!!! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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!!! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| extracting data from one sheet based on data in another - VLookup? | Excel Worksheet Functions | |||
| Need help extracting data | Excel Worksheet Functions | |||
| Extracting data | Excel Worksheet Functions | |||
| Need help extracting data | Excel Discussion (Misc queries) | |||
| Extracting Data for .Txt Files By Unique Field Data | Excel Discussion (Misc queries) | |||
Linear Mode
