Need VBA to prepare data in column to use text-to-column function

[trevhill]

Here's my problem.

I'm using a database software that exports results (in this case the results are industries & subindustries) into one cell. Example

cell A1:A5000

Food;

Meat;

Chicken;

Grower

Each cell has an industry, which is then followed by a semicolon and then a space. When I try to use Excel's built-in text to column feature, I can't get it to work unless I manually enter the cell, and delete the extra spaces between each entry (food; meat; chicken, etc.)

Here's the other probleam, not each row (data point) has the same amount of industries and subindustries. Some may be i) food; meat; chicken; grower and some are just ii) food; meat.

I"m looking for someone to help me draft the VBA code to manipulate the text in colum a, so that I can use the text to column feature.

Thanks for help,

T

 

[VoG]

This sounds very similar - any help? http://www.mrexcel.com/forum/showthread.php?t=587462

 

[trevhill]

I couldn't get the code in the previous link to work (admittedly I'm weak at VBA).

Really my problem is deleteing the extra lines within each cell.

Current data:

Food;

Beverage;

Softdrinks

Next Desired Step:

Food;
Beverage;
Softdrinks

Next Desired Step

Food; Beverage; Softdrinks

At that point it can use the text to columns feature

 

[VoG]

I think that I misunderstood. Is this in a single cell?

Food;

Beverage;

Softdrinks

 

[Charlie45]

Try find>repalce, find what hold down alt and type 010, release alt key This will remove the line feed.

Then do you text to column

 

[trevhill]

Yes, all the data on each row is in a single cell.

 

[trevhill]

Charlie, I don't think I'm doing it correctly. Its not working

 

[Charlie45]

Highlight the column. Select Text to Columns. Select Delimited>Next. Select>Other and in the box do ALT+Enter. Check the Box beside "Treat consecutive delimiter as one" Select Finish.

You should have Food; in 1 column, Meat; in the next Chicken: in the next