CELL FORMAT

  • Thread starter Thread starter FADDRICK REMO
  • Start date Start date
F

FADDRICK REMO

hello,
Can somebody help me in solvin the follwoing problem...

I have a excel sheet which has 10000 customer's
information but not formatted in the sense
name,age,employment together in one cell.

The datas i have, dont have any commas or spaces
and more over all the fields like name,age are together
and broken into 2 or 3 different rows.
i wanted to seperate it and put it in a table format.

the sample data is as follows.
name ddddd last pd date 12/10/2003
age 23 employee ffffff employer fghfghfgh.

the result what iam expecting is as follows.
name lastpddate age employee employer
dddd 12/10/2003 23 fffffff fghfghfgh.

can u help me in solving this problem?
Bye
Faddrick
 
Hi Faddrick,
Is everything actually on one line and you have cell wrap on
(Format, cells, alignment). Try expanding the width of the cell.
If you have consistent line breaks after date, then you probably
have a CHAR(10) carriage return.

Do you still have the data before you pulled it into Excel, it seems
to me that you probably had data that lines up if you use a fixed
font. Try changing that file from having a ".csv" file extension
to ".txt" and then opening it from Excel, which would bring up
the Text Import Wizard, choose fixed length instead of delimited,
and the rest should be obvious. You can move, add, delete
column separators and you can identify the type of data in a
column during the Text Import Wizard.

If the data is only in Excel. Then on a copy of your worksheet,
try Data, Text to Columns. It is the same as the Text Import Wizard.

Be sure to post back if this solves your problem or not.
 
Hello David,
I dont have data before it was transferred it into excel.
iam getting the datas from my client.
i tried your way of converting the file to .txt but still
i couldnt get the expected result.
can u please help me in rectfying this problem.

Bye
Faddrick
-----Original Message-----
Hi Faddrick,
Is everything actually on one line and you have cell wrap on
(Format, cells, alignment). Try expanding the width of the cell.
If you have consistent line breaks after date, then you probably
have a CHAR(10) carriage return.

Do you still have the data before you pulled it into Excel, it seems
to me that you probably had data that lines up if you use a fixed
font. Try changing that file from having a ".csv" file extension
to ".txt" and then opening it from Excel, which would bring up
the Text Import Wizard, choose fixed length instead of delimited,
and the rest should be obvious. You can move, add, delete
column separators and you can identify the type of data in a
column during the Text Import Wizard.

If the data is only in Excel. Then on a copy of your worksheet,
try Data, Text to Columns. It is the same as the Text Import Wizard.

Be sure to post back if this solves your problem or not.


"FADDRICK REMO" <[email protected]>
Click to expand...
wrote in message [email protected]...
 
FADDRICK" <[email protected]>
wrote in message
Dear david ,
Since i can't attach any files here, i have alreday mailed
u the copy of sample data from my personnel mailid.
SO can u kindly chechk your mail id?
Any way iam pasting the sample data.
Iam not sure whether u can understand it or not.

SAMPLE DATA IS AS FOLLOWS.
1) Bank 0488 Acct Nbr 111111111 Product Type 100
Card Nbr 34534534534534534 dfgdfgdfgdfgdfgdfgdf
Card Status 1 XPAC Code XPAC Dte 00/00/0000
2)Tel O/H 03 7465112 X156 /
Dtes: Open 12/06/2000 Exp 06/01 Due 23/12/2003
Delq Cyc 9 Status 1 XPAC C1 XPAC Dte 09/02/2003
Rsn XP Class R Stmt Cyc 03
3)Lmt 3000.00 Auth .00 Bal 3804.91
XD 91.71 30D 87.73 60D 87.28
90D 85.13 120D 81.42 150D 81.00
180D 77.49 210D 3120.97
Current Due 92.18 Total Due 3804.91
Score 00340 Alloc Tbl 5000 Coll CDRAZLEEN Float File N
Date Time Actn Actn Dte Actn Amt Rev Date Time Coll ID
17112003 20084617 AGRT 00000000 .00 00000000 CCDILA
07102003 9413986 AGPL 00000000 .00 000000 CCDCOLL22
07102003 9413985 AGRT 00000000 .00 0000000 CCDCOLL22

Bye
faddrick
 
Did not get your email expect if was discarded for some reason,
Lost an hour this morning trying to find this thread again.
Anonymous postings make things very difficult for the
rest of us, in my opinion. (this is the short version of my opinion)

Can't tell anything from what you pasted into your posting.
 
Dear david,
very very sorry for wasting your precious time.
shall i email it again to your personnel mailid?
Bye
faddrick
-----Original Message-----
Did not get your email expect if was discarded for some reason,
Lost an hour this morning trying to find this thread again.
Anonymous postings make things very difficult for the
rest of us, in my opinion. (this is the short version of my opinion)

Can't tell anything from what you pasted into your posting.




"FADDRICK REMO" <[email protected]>
Click to expand...
wrote in message [email protected]...
 
Dear Mr David,
I have already emiled you the copy of sample data.
Can u kindly check your mailid?

By
Faddrick
 
Nothing yet and it is one hour since you sent according
to the recorded time stamps.
 
Dear Mr David,
Can you really help me ?
Because my client is putting pressure on me everyday.
iam also trying to rectify the problem.
i staretd using macros but it also didnt give me expected
out put.
Pl help me.
Bye
Faddrick remo
 
As I indicated before, I am not receiving your email.

Originally you indicated a problem with some separation
of data in a cell, later you provided a larger text example which hardly
resembles the original message. The original example looked
like one line per customer. The second example looks like a
big report of perhaps 60 lines per customer of which you want
to pick out individual strings from all over the place and add the up.

Since it looks like it came from a text file report, you should be
processing the text file with some language better suited for such
things *before* bringing it into Excel.

When you try to extract data from a formatted report, and make your
own database, you are always subject to the format changing.

A much simpler example of trying to extract data from something
else would be various examples of trying to pull in stock data from
an HTML page -- you can get it work, but you may have to change
the program several times a month. The same site might provide a
download spreadsheet (.csv) option specifically designed for use in
a spreadsheet and not give any problems year to year. Of course
your data is text which is easier to work with than reading HTML
source, but many of the problems of report format changing would
be common.

It is one thing to go through data on a one-time basis and format
it into something else, but when you are dealing with extracting data
from what is intended to be a printed report, you are not likely to
be able to continue doing exactly the same thing on an on-going basis.

What you probably want is to use something like Perl or Snowball or some other
text processing language, none of which I am familiar with. There
are probably some GUI based things that would allow you to pick
out the fields.
 
Back
Top