Splitting rows into columns...

[jamiender]

Hi All

Ive recently got a new job, and I really want to impress my boss by
automating some of the excel work we do - which is where I discovered
macros! Id really appreciate if you guys (and girls) can help me out
here.....

Basically, each day I get an excel sheet like this:

********************************************************************

KAMAL COMPUTERS - London

LC23658 4012250 Eads(Euro Aeronautic Defens 62,400
10/05/2006 01/06/2006
ARGOS GUS - Singapore

LC23493 4121305 Boehler-Uddeholm - Npv 4,000
18/05/2006 26/05/2006 LC24235 6177997 Manulife Financial
Corp - C 30,000 15/05/2006 19/06/2006 LC24406
6624608 National Australia Bank - N 200,000 02/06/2006
13/07/2006 LC24407 6624608 National Australia Bank - N
200,000 02/06/2006 13/07/2006 LC24438 7111314 Inditex
(Ind.De Diseno Text 620,000 21/07/2006 21/07/2006
LC24439 7111314 Inditex (Ind.De Diseno Text 620,000
21/07/2006 21/07/2006 CITIGROUP - London


LC24396 B01Y4N1 Group 4 Securicor - Ord Gbp 16,500
07/06/2006 11/07/2006

*******************************************************************

These are the contents of cells all in column A (but each consecutive
row) i.e "LC23493 4121305 Boehler-Uddeholm - Npv
4,000 18/05/2006 26/05/2006" is the cell content of A4.

As you can see there are three companies (Kamal computers, argos and
citigroup) and under each company there are different references and
dates. What I need to do is split the references and dates up into
different columns, but not the company names

I have tried doing "text to columns" but that doesnt work because it
will split the references up (ie. put LC23493 and 4121305 etc into
consecutive colums) but it also the company name up into columns i.e.
CITIGROUP becomes CITIGR in A1 and OUP in B1.

Basically, whenever the row begins with the LC reference, I need that
row to be split up - but the row with the company names need to not be
split up.

Any ideas?

Thanks so much!!!
Jamie

 

[Guest]

Hey there,
Put these formulas in the columns out next to your 1st "LC line"
Then simply copy all 4 formulas the appropriate rows below.
Assumptions include.
1. the LC# is always 7 characters long
2. the 1st all numeric value (i.e 4012250) is always 7 characters long
3. the 2nd all numeric number (i.e. 62,400) is never longer than 10 characters
4. the 1st 2 gaps in the string always contain 4 spaces

LC23658 =LEFT(A1,7)
4012250 =TRIM(MID(A1,FIND(" ",A1,7)+4,7))
Eads(Euro Aeronautic Defens
=MID(LEFT(A1,FIND(E1,A1)-1),22,LEN(LEFT(A1,FIND(E1,A1)-1))-15)
62,400 =TRIM(RIGHT(A1,10))

 

[jamiender]

Im sorry, I actually have no idea how to do what you just said?!?!?!?
Sorry, I know Im being really dumb here!

All that data is in a single cell (when I copy/paste it onto this forum
it puts it on different lines). I just need to split the data up into
different columns providing the first two letters of the cell contain
the text "LC".

I have been playing around with recording macro's and this is the text
for splitting it up into columns that I want:



**************************************************************************************

Sub actualformatting()


Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(11, 1), Array(24, 1),
Array(51, 1), Array(68, 1), _
Array(82, 1), Array(98, 1), Array(104, 1), Array(125, 1))

End Sub

**************************************************************************************

This is when I selected the entire column A and recorded doing the
"text to columns" option.

How can I change this code so that it goes through each row, and
applies the above formatting if the two beginning characters of the
cell are "LC"?

Cheers guys
Jamie

 

[Guest]

Ah...since the data in all in 1 cell. You will first need to break the data
in to rows. you can do this by:
1. select the cell
2. press F2
3. copy the contents of the cell
4. press 'esc'
5. paste

Now the first row of each section is where we want to point the formulas I
posted for you.