separate text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All!

How to separate a text from the total sentence, with the left array, without
knowing how many characters it contains?

Thank you,
 
Please, Give an example of what you want to do.

General advice, check out the VBA string functions - Left, Right, Mid,
Instr.
 
Beatrix

We aren't there. We can't see what you're working on.

Could you provide an example of what you are trying to do?
 
Dear John!

This is an average address, but the database doesn't separate the text.
for example:
1052 Budapest, blablah street 8.

1052 (zip) okay, 4 digits, simple Left array
Budapest (city) this is the problem, it can be any long, but normally it is
only one word.
blablah street 8. (who cares. :)

Thank you!
 
As far as I remember it had something to do with count array... but if we
could "cut" the first word of the sentence, it would be fine.
 
Beatrix

If you were explaining to someone "how" to "cut the first word of the
sentence", how would they know where the first word ends?

If you'd tell them "go to the first space" to find the first word, you can
tell Access that, too.

You could create a query that includes a field that looks something like:

Expr: Left([YourField],Instr([YourField]," ")

This "breaks" when there is no space in the field -- are you sure you have
spaces in every row?
 
Dear Jeff,
yes, there is, I will also build a control for this to see if all is well.

but let's suppose, there is a space.


Jeff Boyce said:
Beatrix

If you were explaining to someone "how" to "cut the first word of the
sentence", how would they know where the first word ends?

If you'd tell them "go to the first space" to find the first word, you can
tell Access that, too.

You could create a query that includes a field that looks something like:

Expr: Left([YourField],Instr([YourField]," ")

This "breaks" when there is no space in the field -- are you sure you have
spaces in every row?

--
Regards

Jeff Boyce
<Office/Access MVP>

Beatrix said:
As far as I remember it had something to do with count array... but if we
could "cut" the first word of the sentence, it would be fine.
Click to expand...
Click to expand...
 
Assumptions:
There is always a leading mail code followed by a space
There is always a comma after the city name

ZIP: Trim( Left([Address],Instr(1,[Address]," "))

City: Trim(Mid([Address], Instr(1,[Address],"
"),0+Instr(1,[Address],",") -Instr(1,[Address]," ")))

Street: Mid([Address,Instr(1,[Address],",")+1)

You may have to adjust the city calculation a bit by changing the 0+ to some
other number. If it works as is then you can remove the 0+ from the
calculation
 
Dear John!
Thank you very much, this is what I wanted. I don't really understand how it
works, but I'll look it up.
Can you tell me, how to remove the starting space from the "street" function
result you have written?
Thanks,

John Spencer said:
Assumptions:
There is always a leading mail code followed by a space
There is always a comma after the city name

ZIP: Trim( Left([Address],Instr(1,[Address]," "))

City: Trim(Mid([Address], Instr(1,[Address],"
"),0+Instr(1,[Address],",") -Instr(1,[Address]," ")))

Street: Mid([Address,Instr(1,[Address],",")+1)

You may have to adjust the city calculation a bit by changing the 0+ to some
other number. If it works as is then you can remove the 0+ from the
calculation

Beatrix said:
Dear John!

This is an average address, but the database doesn't separate the text.
for example:
1052 Budapest, blablah street 8.

1052 (zip) okay, 4 digits, simple Left array
Budapest (city) this is the problem, it can be any long, but normally it
is
only one word.
blablah street 8. (who cares. :)

Thank you!
Click to expand...
Click to expand...
 
Dear John!

My next problem is, in some cases (this is a stupid database, It wasn't me
who did it...) in front of the addresse there is a name too which cannot be
removed.
I separated these records in a query, and now I'm having problems with the
same questions as above: how to separate the zip and the city when I don't
know, how many spaces are in the "name", but I know, there is a semicolon
after it (before the Zip code)

Thank you


John Spencer said:
Assumptions:
There is always a leading mail code followed by a space
There is always a comma after the city name

ZIP: Trim( Left([Address],Instr(1,[Address]," "))

City: Trim(Mid([Address], Instr(1,[Address],"
"),0+Instr(1,[Address],",") -Instr(1,[Address]," ")))

Street: Mid([Address,Instr(1,[Address],",")+1)

You may have to adjust the city calculation a bit by changing the 0+ to some
other number. If it works as is then you can remove the 0+ from the
calculation

Beatrix said:
Dear John!

This is an average address, but the database doesn't separate the text.
for example:
1052 Budapest, blablah street 8.

1052 (zip) okay, 4 digits, simple Left array
Budapest (city) this is the problem, it can be any long, but normally it
is
only one word.
blablah street 8. (who cares. :)

Thank you!
Click to expand...
Click to expand...
 
Use the trim function. It strips off leading and trailing spaces.
Street: Trim(Mid([Address,Instr(1,[Address],",")+1))



Beatrix said:
Dear John!
Thank you very much, this is what I wanted. I don't really understand how
it
works, but I'll look it up.
Can you tell me, how to remove the starting space from the "street"
function
result you have written?
Thanks,

John Spencer said:
Assumptions:
There is always a leading mail code followed by a space
There is always a comma after the city name

ZIP: Trim( Left([Address],Instr(1,[Address]," "))

City: Trim(Mid([Address], Instr(1,[Address],"
"),0+Instr(1,[Address],",") -Instr(1,[Address]," ")))

Street: Mid([Address,Instr(1,[Address],",")+1)

You may have to adjust the city calculation a bit by changing the 0+ to
some
other number. If it works as is then you can remove the 0+ from the
calculation

Beatrix said:
Dear John!

This is an average address, but the database doesn't separate the text.
for example:
1052 Budapest, blablah street 8.

1052 (zip) okay, 4 digits, simple Left array
Budapest (city) this is the problem, it can be any long, but normally
it
is
only one word.
blablah street 8. (who cares. :)

Thank you!

:

Please, Give an example of what you want to do.

General advice, check out the VBA string functions - Left, Right, Mid,
Instr.
Hi All!

How to separate a text from the total sentence, with the left array,
without
knowing how many characters it contains?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
Beatrix

You'll need to use a query and one/more functions to split the text string
apart. If you have a newer version of Access (I believe 2000, on), take a
look at Access VBA HELP on the Split() function.

--
Regards

Jeff Boyce
<Office/Access MVP>

Beatrix said:
Dear Jeff,
yes, there is, I will also build a control for this to see if all is well.

but let's suppose, there is a space.


Jeff Boyce said:
Beatrix

If you were explaining to someone "how" to "cut the first word of the
sentence", how would they know where the first word ends?

If you'd tell them "go to the first space" to find the first word, you can
tell Access that, too.

You could create a query that includes a field that looks something like:

Expr: Left([YourField],Instr([YourField]," ")

This "breaks" when there is no space in the field -- are you sure you have
spaces in every row?

--
Regards

Jeff Boyce
<Office/Access MVP>

Beatrix said:
As far as I remember it had something to do with count array... but if we
could "cut" the first word of the sentence, it would be fine.

:

Dear John!

This is an average address, but the database doesn't separate the text.
for example:
1052 Budapest, blablah street 8.

1052 (zip) okay, 4 digits, simple Left array
Budapest (city) this is the problem, it can be any long, but
Click to expand...
normally it
is
only one word.
blablah street 8. (who cares. :)

Thank you!

:

Please, Give an example of what you want to do.

General advice, check out the VBA string functions - Left, Right, Mid,
Instr.
Hi All!

How to separate a text from the total sentence, with the left array,
without
knowing how many characters it contains?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
Use the same functions you were shown earlier.

Instr(1,SomeStringToLookAt,SomeStringToFind,3) returns the first locations
of SomeStringToFind in SomeStringToLookAt.

Instr(1,"Holly; 23456",";",3) would return 6 which is the position of the
first (and only) semi-colon.

The "1" is the start position - start looking here
The "3" is basically ignore case when looking

Instr(1,[Address],";",3)

Mid returns a string from another string. It starts at a position and
returns a specified number of characters ( or the rest of the string if no
length is specified. Left and Right work similarly, but only return the
number of characters specified from the left or from the right.

Mid([Address],10) gets all characters in Address from the 10th character on.
Using InStr(...) in place of the "10" gives you a variable start point for
each record

Mid([Address],Instr(1,[Address],";",3) +1)
We add one to the above because we don't want to include the semi-colon.

Beatrix said:
Dear John!

My next problem is, in some cases (this is a stupid database, It wasn't me
who did it...) in front of the addresse there is a name too which cannot
be
removed.
I separated these records in a query, and now I'm having problems with the
same questions as above: how to separate the zip and the city when I don't
know, how many spaces are in the "name", but I know, there is a semicolon
after it (before the Zip code)

Thank you


John Spencer said:
Assumptions:
There is always a leading mail code followed by a space
There is always a comma after the city name

ZIP: Trim( Left([Address],Instr(1,[Address]," "))

City: Trim(Mid([Address], Instr(1,[Address],"
"),0+Instr(1,[Address],",") -Instr(1,[Address]," ")))

Street: Mid([Address,Instr(1,[Address],",")+1)

You may have to adjust the city calculation a bit by changing the 0+ to
some
other number. If it works as is then you can remove the 0+ from the
calculation

Beatrix said:
Dear John!

This is an average address, but the database doesn't separate the text.
for example:
1052 Budapest, blablah street 8.

1052 (zip) okay, 4 digits, simple Left array
Budapest (city) this is the problem, it can be any long, but normally
it
is
only one word.
blablah street 8. (who cares. :)

Thank you!

:

Please, Give an example of what you want to do.

General advice, check out the VBA string functions - Left, Right, Mid,
Instr.
Hi All!

How to separate a text from the total sentence, with the left array,
without
knowing how many characters it contains?

Thank you,
Click to expand...
Click to expand...
Click to expand...
 
Back
Top