Separating Text Field into Columns at Line Break

[Doug T]

I need your help to automate the process to break a text field (with multiple lines separated with the line break character) into separate columns. I've struggled for several days and have seen various code samples using the "Split" function, but nothing that exactly works.

Here's what I have:
1. Canned report that can be exported from a web page as Excel, BUT Excel is losing leading zeros on account numbers. I NEED the leading zeros (i.e., must be pulled in as TEXT and not number).

2. I can preserve the leading zeros by opening the file and saving as HTML, then importing into MS Access. This puts all the fields in the long text field into a single field that looks like this (no quotes in the original text):

COLUMN 1:
"Customer"

COLUMN 2 (5 fields concatenated with line breaks):
"Bob Smith
0001234
ACME Company
123 Main Street
Anywhere, USA"

The fields are always in the same place, so I want to parse it into separate columns (where "|" illustrates a column delimiter):

Bob Smith|0001234|ACME Company|123 Main Street|Anywhere, USA

What I envision is creating a function or procedure that spins through the table and breaks the values into separate columns in the same table. I can also use a query with calculated fields for each column (where I parse out [WholeString] into [StringPart1], [StringPart2]...[StringPart10] (there won't be more than 10 rows in the freeform text).

I'm fairly handly with MS Access, and a beginner at VBA.
Please reply with any further questions. Thanks in advance for your help.

- Doug T.

[OBP]

Doug, is it possible to provide some actual data to "Play" with?
String manipulation in Access is VBA is very powerful and we can easily do what you want.
One of the keys to this is to find the ASCII code of the line Break character, it could be CHR$(10) or Chr$(13) or soemthing more exotic.

[Doug T]

I found a way to accomplish what I need. Here's the function that I call from a query:

Function GetPart(varText As Variant, intPart As Integer, strDelimiter As String) As Variant
Dim a
If IsNull(varText) Then Exit Function
a = Split(varText, strDelimiter)
If (intPart - 1) <= UBound(a) Then
GetPart = a(intPart - 1)
End If
End Function

I call this with a statement (Where [WholeString] is the concatenated value in Column 2):
RowOneValue: GetPart([WholeString],1,Chr(13) & Chr(10))
RowTwoValue: GetPart([WholeString],2,Chr(13) & Chr(10))
...
RowTenValue: GetPart([WholeString],2,Chr(13) & Chr(10))
(I have only a max of 10 rows in [WholeString].

Let me know if you have other ideas. This is working for me so far.

~ Doug T