Parsing a text string

[Guest]

How can I parse a two word string into two seperate strings. The number of
characters in the first word of each string varies? Therefore, I cannot use
the built-in function Left.

Examples:
Japanese J1
ASL A1
English E1

If this data were in Excel, I would simply use the Text-to-Columns command.

 

[Dirk Goldgar]

How can I parse a two word string into two seperate strings. The
number of characters in the first word of each string varies?
Therefore, I cannot use the built-in function Left.

Examples:
Japanese J1
ASL A1
English E1

If this data were in Excel, I would simply use the Text-to-Columns
command.

The Split function will parse it into an array of strings.

 

[Guest]

I thought that it might but I cannot find the split function in the Built In
Function Folder. Where do I find it?

 

[Dirk Goldgar]

I thought that it might but I cannot find the split function in the
Built In Function Folder. Where do I find it?

What version of Access are you using? The Split function was introduced
with Access 2000. How and where are you trying to use the function?

It's not always easy to find things in the help file. Here's an extract
from the help entry for the Split function:

<quote from help>
Description

Returns a zero-based, one-dimensional array containing a specified
number of substrings.

Syntax

Split(expression[, delimiter[, limit[, compare]]])

The Split function syntax has these named arguments:

Part Description
----- ---------------
expression Required. String expression containing substrings
and delimiters. If expression is a zero-length
string(""), Split returns an empty array, that
is, an array with no elements and no data.

delimiter Optional. String character used to identify
substring limits. If omitted, the space
character (" ") is assumed to be the delimiter.
If delimiter is a zero-length string, a
single-element array containing the entire
expression string is returned.

limit Optional. Number of substrings to be returned;
-1 indicates that all substrings are returned.

compare Optional. Numeric value indicating the kind of
comparison to use when evaluating substrings.
See Settings section for values.


Settings
----------

The compare argument can have the following values:

Constant Value Description
----------- ------- ---------------
vbUseCompareOption -1 Performs a comparison using the
setting of the
Option Compare
statement.

vbBinaryCompare 0 Performs a binary comparison.

vbTextCompare 1 Performs a textual comparison.

vbDatabaseCompare 2 Microsoft Access only. Performs a
comparison based on
information in your
database.
</quote from help>

 

[Mike Labosh]

I thought that it might but I cannot find the split function in the Built

In
Function Folder. Where do I find it?

It is a globally visible function.

Search help for "Split Function" You can use it any place in Access, but
the expression builder dialog actually does not list all the things you
might want to put into an expression.

And since you ask, You will specifically find it in the VBA.Strings module
which is part of VBA.
--


Peace & happy computing,

Mike Labosh, MCSD MCT
Owner, vbSensei.Com

"Escriba coda ergo sum." -- vbSensei

 

[Guest]

Dick,

Thanks for the help. I'm using Access 2003 with SP2 installed.

Previously, I had located the reference to Split Function in the Help file.

I have been trying to access the function using the Expression Builder
Wizard to create a calculated field with the first word of the array.

Split does not show up in the Built In Functions folder when I access it via
the wizard using the "Show All Functions."

I have run Detect and Repair and still cannot find the function.

Will the function work if I simply key it in as you have indicated?


--
CIM

I thought that it might but I cannot find the split function in the
Built In Function Folder. Where do I find it?

What version of Access are you using? The Split function was introduced
with Access 2000. How and where are you trying to use the function?

It's not always easy to find things in the help file. Here's an extract
from the help entry for the Split function:

<quote from help>
Description

Returns a zero-based, one-dimensional array containing a specified
number of substrings.

Syntax

Split(expression[, delimiter[, limit[, compare]]])

The Split function syntax has these named arguments:

Part Description
----- ---------------
expression Required. String expression containing substrings
and delimiters. If expression is a zero-length
string(""), Split returns an empty array, that
is, an array with no elements and no data.

delimiter Optional. String character used to identify
substring limits. If omitted, the space
character (" ") is assumed to be the delimiter.
If delimiter is a zero-length string, a
single-element array containing the entire
expression string is returned.

limit Optional. Number of substrings to be returned;
-1 indicates that all substrings are returned.

compare Optional. Numeric value indicating the kind of
comparison to use when evaluating substrings.
See Settings section for values.


Settings
----------

The compare argument can have the following values:

Constant Value Description
----------- ------- ---------------
vbUseCompareOption -1 Performs a comparison using the
setting of the
Option Compare
statement.

vbBinaryCompare 0 Performs a binary comparison.

vbTextCompare 1 Performs a textual comparison.

vbDatabaseCompare 2 Microsoft Access only. Performs a
comparison based on
information in your
database.
</quote from help>

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Steve]

String1 = Left([TwoWordString],Instr([TwoWordString]," ")-1)
String2 =Mid([TwoWordString],Instr([TwoWordString]," ")+1)

 

[StopThisAdvertising]

"Steve" <[email protected]> schreef in bericht
--

PC Datasheet
Your Resource For Help With Access, Excel And Word Applications 'Resource ????
Over 1150 users have come to me from the newsgroups requesting help '1150 users ????
(e-mail address removed)

--
To Steve:
Over 525!! users from the newsgroups have visited the website to read what kind of a 'resource' you are... (rapidly increasing..)

To the original poster:

Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R