Split Field into two new fields

[bigboab5]

Hi guys,

Wonder if any of you can help me. I have an access database (2007), its very simple, only 2 fields. The problem is I want to split one of the fields into 2 separate fields. The field is a text field which is always in this format:

[xxxxx] yyyyyyyyy (sample: [Displ] Cold-Pheonix )

The xxxxx can be any length but is typically 5 or 6 characters
The yyyyyyyy can be no more than 20 characters.
There is always a space after the ]
The xxxxx is always surrounded by [ ]

So how can I split this into 2 new fields with [xxxxx] in one field and yyyyyyyyy in another?

Any help would be most appreciated.

 

[Peter Reid]

Assuming your text field is called field1

Left([field1],InStr([Field1],"]"))

and

Right([field1],Len([Field1])-InStr([Field1],"]")-1)

 

[Mike375]

In your query make two new calculated fields. To do that just enter a field name on the field row followed by : and then the expression.

In the following abc is your field name containing the joined data

FromTheLeft:Left([abc],InStr(1,[abc]," ")-1)

FromTheRight:Right(Trim([abc]),Len(Trim([abc]))-InStr(1,[abc]," "))

That will put [Displ] in the left and Cold-Pheonix in the right.

The brackets don't matter since you want those included in your result.

 

[bigboab5]

Thanks guys, worked a treat, worked best wi the Trim function best. I was at this for ages, so again thanks for your speedy replies.

Boab