in my excel sheet ... on colA , i have these values.
these are pasted from notepad.. i have to write a macro to split the
values in the coumns
PAGE 1
WORK DEPOSIT HOURS OTHRS DTHRS WAGES
PERIOD DATE SSN EMPLOYEE NAME HOURS RATE OTHRS RATE DTHRS RATE WAGES
RATE AMOUNT

1606 M1601 TEMPLATE PERSON ONE
---------------------------------------------------------------------------------------------------------------------------------------------------


3/31/2003 6/02/2003 088-58-6287 DUNCAN THOMAS H 140.00 4.950 0.00 0.00
693.00
3/31/2003 6/02/2003 273-58-2260 LANGLEY STEPHEN MI 72.00 4.950 0.00
0.00 356.40
3/31/2003 6/02/2003 307-14-1923 ROSAS FRANCO 143.50 4.950 0.00 0.00
710.33
3/31/2003 6/02/2003 318-78-2627 GOODWIN STEVEN P 125.00 4.950 0.00 0.00
618.75


TOTAL

GRAND TOTAL

PAGE 2
WORK DEPOSIT HOURS OTHRS DTHRS WAGES
PERIOD DATE SSN EMPLOYEE NAME HOURS RATE OTHRS RATE DTHRS RATE WAGES
RATE AMOUNT

1601 TEMPLATE PERSON TWO

---------------------------------------------------------------------------------------------------------------------------------------------------



3/31/2003 6/02/2003 318-82-3479 WITTEKINDT LOWELL A 160.00 4.950 0.00
0.00 792.00
3/31/2003 6/02/2003 322-88-0428 ESPINO FELICIANO 152.00 4.950 0.00 0.00
752.40
3/31/2003 6/02/2003 324-76-4703 SHIMP MARK 127.00 4.950 0.00 0.00
628.65



How can i find the record is started and i have to use the data split
sub for the rows ..

'sub to split the column wise data
Range("A" & intRow ).Select
Selection.TextToColumns Destination:=Range("A13"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(13, 1), Array(24, 1), Array(37, 1),
Array(69, 1), _
Array(75, 1), Array(85, 1), Array(98, 1), Array(125, 1)),
TrailingMinusNumbers:=True


--
nshanmugaraj
------------------------------------------------------------------------
nshanmugaraj's Profile: http://www.excelforum.com/member.php...o&userid=31570
View this thread: http://www.excelforum.com/showthread...hreadid=518948

Have you tried DataText To Columns with a space delimiter?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"nshanmugaraj"
wrote in message
news:nshanmugaraj.244yeo_1141459806.2322@excelforu m-nospam.com...

in my excel sheet ... on colA , i have these values.
these are pasted from notepad.. i have to write a macro to split the
values in the coumns
PAGE 1
WORK DEPOSIT HOURS OTHRS DTHRS WAGES
PERIOD DATE SSN EMPLOYEE NAME HOURS RATE OTHRS RATE DTHRS RATE WAGES
RATE AMOUNT

1606 M1601 TEMPLATE PERSON ONE
--------------------------------------------------------------------------
-------------------------------------------------------------------------


3/31/2003 6/02/2003 088-58-6287 DUNCAN THOMAS H 140.00 4.950 0.00 0.00
693.00
3/31/2003 6/02/2003 273-58-2260 LANGLEY STEPHEN MI 72.00 4.950 0.00
0.00 356.40
3/31/2003 6/02/2003 307-14-1923 ROSAS FRANCO 143.50 4.950 0.00 0.00
710.33
3/31/2003 6/02/2003 318-78-2627 GOODWIN STEVEN P 125.00 4.950 0.00 0.00
618.75


TOTAL

GRAND TOTAL

PAGE 2
WORK DEPOSIT HOURS OTHRS DTHRS WAGES
PERIOD DATE SSN EMPLOYEE NAME HOURS RATE OTHRS RATE DTHRS RATE WAGES
RATE AMOUNT

1601 TEMPLATE PERSON TWO

--------------------------------------------------------------------------
-------------------------------------------------------------------------



3/31/2003 6/02/2003 318-82-3479 WITTEKINDT LOWELL A 160.00 4.950 0.00
0.00 792.00
3/31/2003 6/02/2003 322-88-0428 ESPINO FELICIANO 152.00 4.950 0.00 0.00
752.40
3/31/2003 6/02/2003 324-76-4703 SHIMP MARK 127.00 4.950 0.00 0.00
628.65



How can i find the record is started and i have to use the data split
sub for the rows ..

'sub to split the column wise data
Range("A" & intRow ).Select
Selection.TextToColumns Destination:=Range("A13"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(13, 1), Array(24, 1), Array(37, 1),
Array(69, 1), _
Array(75, 1), Array(85, 1), Array(98, 1), Array(125, 1)),
TrailingMinusNumbers:=True


--
nshanmugaraj
------------------------------------------------------------------------
nshanmugaraj's Profile:
http://www.excelforum.com/member.php...o&userid=31570
View this thread: http://www.excelforum.com/showthread...hreadid=518948

mr. Bob Phillips

yes . i tried. but the contents of the main column cell are also
replaced. so i need not chage the heading . i want to find in which row
the record is and then only to assign the data to column.


--
nshanmugaraj
------------------------------------------------------------------------
nshanmugaraj's Profile: http://www.excelforum.com/member.php...o&userid=31570
View this thread: http://www.excelforum.com/showthread...hreadid=518948

Have you tried importing the file instead of pasting it in from notepad. That
way you can select which lines and spaces are to be excluded.

"nshanmugaraj" wrote:


mr. Bob Phillips

yes . i tried. but the contents of the main column cell are also
replaced. so i need not chage the heading . i want to find in which row
the record is and then only to assign the data to column.


--
nshanmugaraj
------------------------------------------------------------------------
nshanmugaraj's Profile: http://www.excelforum.com/member.php...o&userid=31570
View this thread: http://www.excelforum.com/showthread...hreadid=518948