How do i split text from 1 column into 3 columns

[Sommerfugl1999]

I have a text "Frederiksborgvej 108 2. Tv " i would like to split into 3
columns like
"Frederiksborgvej" and "108" and "2. Tv".
How do I do that?
Thanks for your help

 

[Fred]

I'm assuming that you mean split into three FIELDS.

And that you want to do it in some automated fashion.

The first step is that you have to decide what the "rules" are for
splitting, i.e. which portions of the text go into which fields, or what
constitutes the "dividers" in the text string.

Until you define that (= narrow it down) , there are too many possibilities
to discuss.

 

[Jeff Boyce]

One time or for a number of records?

Can you guarantee that they are "space-delimited"? ... or all
fields/columns are exactly the same length?

You could try importing the text and defining the import as space-delimited
(so Access decides the next field/column begins after the spaces...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

It depends. How is the data structured?

If it is ALWAYS (no deviation allowed)
a name followed by a space followed by a code followed by a space followed
by the remainder then.

The following may work. Done off the top of my head, so there could be errors
in the logic or syntax errors - unmatched parentheses. Play around with this
until you get it to work.


Part1: Trim(Left(TheText,Instr(1,TheText," ")))

Part2: Mid(TheText, Instr(1,TheText," ")+1, Instr(Instr(1,TheText,"
")+1,TheText," ")-Instr(1,TheText," ")))

Part3: Trim(Mid(TheText,Instr(Instr(1,TheText," ")+1,TheText," ")))

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County