Break Apart Text

[Bryan]

What is the code to break appart text. I have an address that I would like
to break down at each space " " and send each word in the address to a
diffrent text box.

 

[fredg]

What is the code to break appart text. I have an address that I would like
to break down at each space " " and send each word in the address to a
diffrent text box.

At each space?

So what would you expect to happen with addresses like:

1256 Elm Avenue
and
1256 1/2 South Los Angeles Street, Apt. B

In any event if you have Access 2000 or newer you can use the Split
function or the Left, Mid, Right, and Instr functions with any
version of Access.
Look them all up in VBA help.
In any event, all the data must be in similar order.

 

[Bryan]

I have and bunch of diffrent address and I would like to send each part of
the address to a diffrent text box, splitting it at the space. So if I had
the address 121 Road House Way it would look like this.

Text1=121
Text2=Road
Text3=House
Text4=Way
Text5=
Text6=

Text 5 and 6 are blank because this address does not have any more words to
split. I am using
=Left([AppAddress],InStr([AppAddress]," ")-1) for text1 but don't know how
to move beyond that. I must go in order or this will not work. I am sending
the data to another formula.

 

[John Nurick]

Hi Bryan,

If you're using a recent version of Access, the Split() function is what
you need.

 

[Bryan]

Can you give me a sample because I am not that good with functions?

 

[fredg]

I have and bunch of diffrent address and I would like to send each part of
the address to a diffrent text box, splitting it at the space. So if I had
the address 121 Road House Way it would look like this.

Text1=121
Text2=Road
Text3=House
Text4=Way
Text5=
Text6=

Text 5 and 6 are blank because this address does not have any more words to
split. I am using
=Left([AppAddress],InStr([AppAddress]," ")-1) for text1 but don't know how
to move beyond that. I must go in order or this will not work. I am sending
the data to another formula.

At each space?

So what would you expect to happen with addresses like:

1256 Elm Avenue
and
1256 1/2 South Los Angeles Street, Apt. B

In any event if you have Access 2000 or newer you can use the Split
function or the Left, Mid, Right, and Instr functions with any
version of Access.
Look them all up in VBA help.
In any event, all the data must be in similar order.

Copy and past the following function into a Module:

Public Function ParseText(TextIn As String, x) As Variant
On Error Resume Next
Dim VAR As Variant
VAR = Split(TextIn, " ", -1)
ParseText = VAR(x)

End Function
==============

Then you can call it from a query:

FirstPartarseText([Address],0)
SecondPartarseText([Address],1)
.....
NinthPartarseText([Address],8)
etc.

Notice that the Split function is Zero based.

 

[Bryan]

I could not figure out how to call it from a query but I made it work from a
text box!!! THANK YOU!!!

I have and bunch of diffrent address and I would like to send each part of
the address to a diffrent text box, splitting it at the space. So if I had
the address 121 Road House Way it would look like this.

Text1=121
Text2=Road
Text3=House
Text4=Way
Text5=
Text6=

Text 5 and 6 are blank because this address does not have any more words to
split. I am using
=Left([AppAddress],InStr([AppAddress]," ")-1) for text1 but don't know how
to move beyond that. I must go in order or this will not work. I am sending
the data to another formula.

On Wed, 8 Feb 2006 13:23:28 -0800, Bryan wrote:

What is the code to break appart text. I have an address that I would like
to break down at each space " " and send each word in the address to a
diffrent text box.

At each space?

So what would you expect to happen with addresses like:

1256 Elm Avenue
and
1256 1/2 South Los Angeles Street, Apt. B

In any event if you have Access 2000 or newer you can use the Split
function or the Left, Mid, Right, and Instr functions with any
version of Access.
Look them all up in VBA help.
In any event, all the data must be in similar order.

Copy and past the following function into a Module:

Public Function ParseText(TextIn As String, x) As Variant
On Error Resume Next
Dim VAR As Variant
VAR = Split(TextIn, " ", -1)
ParseText = VAR(x)

End Function
==============

Then you can call it from a query:

FirstPartarseText([Address],0)
SecondPartarseText([Address],1)
.....
NinthPartarseText([Address],8)
etc.

Notice that the Split function is Zero based.

 

[John Nurick]

If the text you want to split is in a string variable strText, you can
do something like this:

Dim arWords As Variant

arWords = Split(strText, " ")

arWords now contains an array with one element for each word in the
string. The number of elements (and therefore of words) is
UBound(arWords) + 1

arWords(0) is the first word,
arWords(1) the second word, and so on.

arWords(UBound(arWords)) is the last word
arWords(UBount(arWords) - 1) the second-last word, and so on.

If you want to extract individual words from a text field in a query,
you can use this little function:

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range

SafeSplit = Split(V, Delim)(Item)
End Function

To use it, create a new module (standard module, not a class
module). Name it something like vbaFunctions. If
Option Explicit
doesn't appear at the top, type it in. Then paste in the SafeSplit()
function.

In your query, use calculated fields like this:

FirstWord: SafeSplit([MyField], " ", 0)
SecondWord: SafeSplit([MyField], " ", 1)

and so on.