Hi,

A quick play with your file, opened in MS Word, replaced " " (3
spaces) by tab, saveas DOS text. In Excel, Data, import external, in
Wizard take Tab and colon : as delimiters, treat consecutive delimiters
as one, into A1

That gave A to L in the attached.

There were two spaces, not 1, in front of your data.

Set the formula in H2 as

=IF(LEFT(B2,2)=" ",MID(B2,3,999),IF(LEFT(B2,1)="
",MID(B2,2,999),IF(B2<"",B2,"")))

copied across, and bulk copied down.

That gave columns H to L

With that range still selected, did Copy and Paste Special Values into
cell N2

That gave columns N O P and Q as your partially cleaned data.

Columns A to M can be deleted, they were just to show.

Does this help?

will try the new formula n that

--

schnett Wrote:
I want to thank you for all that formula coding that went into that
attachment sheet.

This raw attachment is only a sample of the records. The records seem
more regular than irregular.

I tried what you had suggested and it would have worked except there is
a blank space in front of all my characters. Here is the raw data file.
Can you teach me what the formulas in order to extract data from these
records just like you did before ?

Thank you !


+-------------------------------------------------------------------+
|Filename: Spc.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4669 |
+-------------------------------------------------------------------+

--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=534393