Transform a memo field into columns

[DaveT99]

I have copied some data from an online catalogue and have it in a memo field (one record)



is it possible to transform the data into a single column by separating each text item by the space

I have attached a pdf to show table and part of the meme field

Thanks

[Welshgasman]

What do you class as each text item?

B4-94 xxxxxx
or
B4-94
xxxxxx

I would use Split() with the relevant divider. That is up to you.

[DaveT99]

What do you class as each text item?

B4-94 xxxxxx
or
B4-94
xxxxxx

I would use Split() with the relevant divider. That is up to you.

tHE 2ND OPTION, ACTUALLY ONLY WANT THE xxxxxx VALUES BUT I GET ALL AS PER YOUR 2ND OPTION EASY TO GET RID OF THE UNWANTED

IS split () IS THAT THE FIELD NAME TO GO INTO THE BRACKETS, E.G SPLIT (P/n)

tHANKS
dAVE

[CJ_London]

You do this in vba

dim a() as string
dim i as integer
 
a=split(fieldname," ")
for i=0 to ubound (a)
    debug. Print a(i) 
    ' do whatever here - add to a string, append to a table, etc
    i=i+1
next i

edit now on a computer so corrected

step alternative

dim a() as string
dim i as integer
 
a=split(fieldname," ")
for i=0 to ubound (a) step 2
    debug. Print a(i)
    ' do whatever here - add to a string, append to a table, etc

next i

[Welshgasman]

tHE 2ND OPTION, ACTUALLY ONLY WANT THE xxxxxx VALUES BUT I GET ALL AS PER YOUR 2ND OPTION EASY TO GET RID OF THE UNWANTED

IS split () IS THAT THE FIELD NAME TO GO INTO THE BRACKETS, E.G SPLIT (P/n)

tHANKS
dAVE

So I would use @CJ_London's logic, but loop from 1 and step 2

He also meant UBound, not unbound

[CJ_London]

oops - problem with doing the from a phone with autocorrect

[Welshgasman]

oops - problem with doing the from a phone with autocorrect

I know, annoying as hell.

[Ken Sheridan]

ACTUALLY ONLY WANT THE xxxxxx VALUES

In that case I'd include the constant B4-94 in the divider when calling the Split function. You can see how it works in the immediate window:

CatListing = "B4-94 12345 B4-94 23456 B4-94 34567 B4-94 45678 B4-94 56789"
a = Split(CatListing,"B4-94 ")
? Trim(a(1))
12345
? Trim(a(2))
23456
? Trim(a(3))
34567
? Trim(a(4))
45678
? Trim(a(5))
56789

In reality you'd loop through a recordset based on your table, and for each row in the recordset loop through the array and insert each value into a row in a new referencing table, along with a foreign key value referencing the primary key of the current row in the recordset.

[Welshgasman]

Not all B4-94 though Ken?

[Ken Sheridan]

Not all B4-94 though Ken?

Doh! You're absolutely right. At 79 I'm having some difficulty reading the PDF image.

[jojowhite]

you can save the p/n's to a table (table tblPN, with short text field PN).

Private Sub t()
Dim db As DAO.Database
Dim rsSource As DAO.Recordset
Dim rsTarget As DAO.Recordset
Dim pn$, i&, j&, var, p$
Set db = CurrentDb
' open your Source table here
Set rsSource = db.OpenRecordset("select [p/n] from Dodge;")
' open the table where you want to save (tblPN)
Set rsTarget = db.OpenRecordset("tblPN")
With rsSource
    If Not .EOF Then
        .MoveFirst
    End If
    Do Until .EOF
        pn = RTrim$(LTrim$(![p/n] & ""))
        ' remove extra space if there is
        Do While InStr(1, pn, "  ") <> 0
            ' replace double space with single space
            pn = Replace$(pn, "  ", " ")
        Loop
        ' loop until all alphanumeric is found
        var = Split(pn, " ")
        i = UBound(var)
        j = 0: p = ""
        Do While True
            If j > i Then
                If Len(p) <> 0 Then
                    rsTarget.AddNew
                    rsTarget!pn = p
                    rsTarget.Update
                End If
                Exit Do
            End If
            p = p & " " & var(j)
            If IsNumeric(var(j)) Then
                rsTarget.AddNew
                rsTarget!pn = p
                rsTarget.Update
                p = ""
            End If
            j = j + 1
        Loop
        .MoveNext
    Loop
End With
rsSource.Close: rsTarget.Close
Set rsSource = Nothing: Set rsTarget = Nothing: Set db = Nothing
End Sub