How to parse long spaced entry into multiple cells???

[Robert Blass]

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!

 

[Rick Rothstein]

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?

 

[Gord Dibben]

I get the split into 14 columns A through N using space delimited.


Gord Dibben MS Excel MVP

 

[Robert Blass]

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.

 

[Robert Blass]

I'm not sure what you mean? could you explain some?


thanks

 

[Ashish Mathur]

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

 

[Pete_UK]

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

 

[Stephen Bye]

Select column A, then use the Data > Text to Columns menu option and tell it
how you want the items to be separated.

 

[Gord Dibben]

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

 

[Robert Blass]

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???????????

 

[Robert Blass]

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