Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
Using Execl 97'
The labels for each piece of the line of data would be as follows. Year, Brand name, Length, Filter, Package, Strength, Flavor, Style, Package Type, TAR, Nicotine, Caron Monoxide, UPC Code1, UPC Code2 That is 17 separate labels, but they would be coming from one long string in ONE cell, here are a few examples. cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 Some have commas , maybe 1% at best, so you can see how hard it would be to set their ,line by line, translating these..driving me crazy.. Is there a simple way to convert that long string in each cell into 14 different cells represented by the labels I mentioned earlier? thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
You will need to give us just a little more information. Can any entries in
any of your fields (for example, "Brand Name") have spaces in them? You didn't show an example, but for any of the data having commas, is there a space next to it... or is the comma the only delineation between fields? -- Rick (MVP - Excel) "Robert Blass" wrote in message ... Using Execl 97' The labels for each piece of the line of data would be as follows. Year, Brand name, Length, Filter, Package, Strength, Flavor, Style, Package Type, TAR, Nicotine, Caron Monoxide, UPC Code1, UPC Code2 That is 17 separate labels, but they would be coming from one long string in ONE cell, here are a few examples. cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 Some have commas , maybe 1% at best, so you can see how hard it would be to set their ,line by line, translating these..driving me crazy.. Is there a simple way to convert that long string in each cell into 14 different cells represented by the labels I mentioned earlier? thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
I get the split into 14 columns A through N using space delimited.
Gord Dibben MS Excel MVP On Thu, 19 Mar 2009 20:34:06 -0400, Robert Blass wrote: Using Execl 97' The labels for each piece of the line of data would be as follows. Year, Brand name, Length, Filter, Package, Strength, Flavor, Style, Package Type, TAR, Nicotine, Caron Monoxide, UPC Code1, UPC Code2 That is 17 separate labels, but they would be coming from one long string in ONE cell, here are a few examples. cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 Some have commas , maybe 1% at best, so you can see how hard it would be to set their ,line by line, translating these..driving me crazy.. Is there a simple way to convert that long string in each cell into 14 different cells represented by the labels I mentioned earlier? thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
On Thu, 19 Mar 2009 20:58:07 -0400, "Rick Rothstein"
sayd the following: You will need to give us just a little more information. Can any entries in any of your fields (for example, "Brand Name") have spaces in them? You didn't show an example, but for any of the data having commas, is there a space next to it... or is the comma the only delineation between fields? The data would be as follows Each piece of data would follow with a separating SPACE until the very end and Commas would be treated as another single SPACE to sepearate the piece of data until end of line is reached. "Brand type this that and that again" example "brand,style,yes,no" would then read "brand style yes no" If I didn't make that clear, ask again. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
I'm not sure what you mean? could you explain some?
thanks On Thu, 19 Mar 2009 17:58:45 -0700, Gord Dibben <gorddibbATshawDOTca sayd the following: I get the split into 14 columns A through N using space delimited. Gord Dibben MS Excel MVP On Thu, 19 Mar 2009 20:34:06 -0400, Robert Blass wrote: Using Execl 97' The labels for each piece of the line of data would be as follows. Year, Brand name, Length, Filter, Package, Strength, Flavor, Style, Package Type, TAR, Nicotine, Caron Monoxide, UPC Code1, UPC Code2 That is 17 separate labels, but they would be coming from one long string in ONE cell, here are a few examples. cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 Some have commas , maybe 1% at best, so you can see how hard it would be to set their ,line by line, translating these..driving me crazy.. Is there a simple way to convert that long string in each cell into 14 different cells represented by the labels I mentioned earlier? thanks! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
Hi,
He is saying that you navigate to Data Text to columns and select the delimiter as space. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "Robert Blass" wrote in message ... I'm not sure what you mean? could you explain some? thanks On Thu, 19 Mar 2009 17:58:45 -0700, Gord Dibben <gorddibbATshawDOTca sayd the following: I get the split into 14 columns A through N using space delimited. Gord Dibben MS Excel MVP On Thu, 19 Mar 2009 20:34:06 -0400, Robert Blass wrote: Using Execl 97' The labels for each piece of the line of data would be as follows. Year, Brand name, Length, Filter, Package, Strength, Flavor, Style, Package Type, TAR, Nicotine, Caron Monoxide, UPC Code1, UPC Code2 That is 17 separate labels, but they would be coming from one long string in ONE cell, here are a few examples. cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 Some have commas , maybe 1% at best, so you can see how hard it would be to set their ,line by line, translating these..driving me crazy.. Is there a simple way to convert that long string in each cell into 14 different cells represented by the labels I mentioned earlier? thanks! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
Highlight column A by clicking the A identifier at the top of the
column, then click on Data | Text-to-columns and in the wizard select Delimited text with space as the delimiter. Work your way through the 3 panels of the wizard and when you click Finish your data will be parsed for you. If I understand your later post correctly, if you have spaces in a brand name these appear as commas, so after you have parsed the main text you can just highlight the brand name column and use Find/Replace (CTRL-H) to replace a comma with a space. Hope this helps. Pete On Mar 20, 1:21*am, Robert Blass wrote: I'm not sure what you mean? could you explain some? thanks On Thu, 19 Mar 2009 17:58:45 -0700, Gord Dibben <gorddibbATshawDOTca sayd the following: I get the split into 14 columns A through N *using space delimited. Gord Dibben *MS Excel MVP On Thu, 19 Mar 2009 20:34:06 -0400, Robert Blass wrote: Using Execl 97' The labels for each piece of the line of data would be as follows. Year, Brand name, Length, Filter, Package, Strength, Flavor, Style, Package Type, TAR, Nicotine, Caron Monoxide, UPC Code1, UPC Code2 That is 17 separate labels, but they would be coming from one long string in ONE cell, here are a few examples. cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 Some have commas , maybe 1% at best, so you can see how hard it would be to set their ,line by line, translating these..driving me crazy.. Is there a simple way to convert that long string in each cell into 14 different cells represented by the labels I mentioned earlier? thanks!- Hide quoted text - - Show quoted text - |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
Select column A, then use the Data Text to Columns menu option and tell it
how you want the items to be separated. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
I'm also not sure after re-reading your original.
Is the sample data cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 what you have or what you want? Gord On Thu, 19 Mar 2009 21:21:49 -0400, Robert Blass wrote: I'm not sure what you mean? could you explain some? thanks On Thu, 19 Mar 2009 17:58:45 -0700, Gord Dibben <gorddibbATshawDOTca sayd the following: I get the split into 14 columns A through N using space delimited. Gord Dibben MS Excel MVP On Thu, 19 Mar 2009 20:34:06 -0400, Robert Blass wrote: Using Execl 97' The labels for each piece of the line of data would be as follows. Year, Brand name, Length, Filter, Package, Strength, Flavor, Style, Package Type, TAR, Nicotine, Caron Monoxide, UPC Code1, UPC Code2 That is 17 separate labels, but they would be coming from one long string in ONE cell, here are a few examples. cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 Some have commas , maybe 1% at best, so you can see how hard it would be to set their ,line by line, translating these..driving me crazy.. Is there a simple way to convert that long string in each cell into 14 different cells represented by the labels I mentioned earlier? thanks! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to parse long spaced entry into multiple cells???
On Fri, 20 Mar 2009 07:45:42 -0700, Gord Dibben <gorddibbATshawDOTca
sayd the following: I'm also not sure after re-reading your original. Is the sample data cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 what you have or what you want? Just before "286212 9 BJ4X05" there is the Nicotine, Tar and CO 'carbon monoxide' listed in each row. I'm trying to extract the Brand Name, style,lenght of cigarette and then ONLY extract the Nicotine, Tar and CO for that brand in that ROW. I don't know how to explain it any better than that, can someone help??????????? |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to Parse long Row entry into multiple cells ??
Using Execl 97'
The labels for each piece of the line of data would be as follows. Year, Brand name, Length, Filter, Package, Strength, Flavor, Style, Package Type, TAR, Nicotine, Carbon Monoxide, UPC Code1, UPC Code2 That is 17 separate labels, but they would be coming from one long string in ONE cell, here are a few examples. cell a1 = 2005 BASIC KING F SP FULL-FLA S 15 1.1 15 0 286212 9 BJ4X05 cell a2 = 2005 BASIC 100 F SP FULL-FLA S 15 1.1 16 0 286232 3 BJ8X05 cell a3 = 2005 BASIC 100 F SP LT S 10 0.8 13 0 286272 1 BX8X05 I'm only trying to exclude all that other junk in each ROW, but KEEP the Name of the cigarette, style, length, and then Keep the most important data Nicotine, Tar and CO 'carbon monoxide'. The list is super-long and doing it line by line would take me 1,000 years, I'm not a trained typist. Is there a formula or???? thanks again |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Wrap long sentence in multiple cells | Excel Discussion (Misc queries) | |||
change a spreadsheet from single spaced to double spaced? | Excel Worksheet Functions | |||
parse comma separated text to multiple cells and remove duplicates | Excel Worksheet Functions | |||
chart ledgend looks double spaced. How do I fix to single spaced? | Charts and Charting in Excel | |||
average of spaced cells | Excel Worksheet Functions |