Seperate text string

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

Guest

Hi all

Whilst it's easy to find the left or right portion of a text string
Something: Left([CD1stName],1) - 1st from left
Something: Right([CD1stName],1) - 1st from right
or even
Something: Left([CD1stName],2) - the 1st 2 diggits
Something: Right([CD1stName],17) - the last 17 digits
or whatever

Anyone any ideas how to seperate a text string so that I can find the 3rd
digit (number or text) or the 5th or even the 23rd, etc.

example
Postcode (zipcode) = WC2A 2WT
(made up postcode in case you happen to live there)
I need a seperate report field to show C and another to show W, or T, etc.

Basically I need a report to show each seperate digit

Many thanks

Oh this is due to the rather silly "new and improved" method the UK post
office has for dealing with pre-paid recorded bulk mailings (we are sending
out over 150,000). We have to seperate each section of the postcode.
 
If the field is consistant, no blanks, and always the same length..then
simply use mid


Something: Mid([CD1stName],10,1) - start at 10 for one character
 
Thank you Albert

The problems are UK postcode are not the same length

ie.
Manchester M60 1AB
Outter London NE12 2ZX
Inner London SW3B 7TU
Belfast BT1 1AB

etc, etc, etc.

The only common string is that they all have a space then 3 diggits on the
right.

As you can see - slightly confusing
 
Hi Brendon,

I did create a small fuction this morning but it seems to fall down on the
spaces between the diggits

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Brendan Reynolds said:
? Mid$("WC2A 2WT", 2, 1)
C

See the 'Mid Function' help topic for details.

--
Brendan Reynolds
Access MVP


Wayne-I-M said:
Hi all

Whilst it's easy to find the left or right portion of a text string
Something: Left([CD1stName],1) - 1st from left
Something: Right([CD1stName],1) - 1st from right
or even
Something: Left([CD1stName],2) - the 1st 2 diggits
Something: Right([CD1stName],17) - the last 17 digits
or whatever

Anyone any ideas how to seperate a text string so that I can find the 3rd
digit (number or text) or the 5th or even the 23rd, etc.

example
Postcode (zipcode) = WC2A 2WT
(made up postcode in case you happen to live there)
I need a seperate report field to show C and another to show W, or T, etc.

Basically I need a report to show each seperate digit

Many thanks

Oh this is due to the rather silly "new and improved" method the UK post
office has for dealing with pre-paid recorded bulk mailings (we are
sending
out over 150,000). We have to seperate each section of the postcode.
Click to expand...
Click to expand...
 
Assuming Access 2000 or later ...

? Mid$(Replace("W C 2 A 2 W T"," ",""), 2, 1)
C

Re the variable length issue ...

Public Sub TestSub()

Const TestString As String = "W C 2 A 2 W T"

Dim lngLoop As Long
For lngLoop = 1 To Len(Replace(TestString, " ", ""))
Debug.Print Mid$(Replace(TestString, " ", ""), lngLoop, 1)
Next lngLoop

End Sub

Result in the Immediate Window ...

testsub
W
C
2
A
2
W
T

BTW: This is just quick-and-dirty demo code. In a finished app, it would be
more efficient to call Replace before entering the loop and assign the
result to a variable, then use the variable within the loop, to avoid
multiple calls to Replace within the loop.

--
Brendan Reynolds
Access MVP

Wayne-I-M said:
Hi Brendon,

I did create a small fuction this morning but it seems to fall down on the
spaces between the diggits

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Brendan Reynolds said:
? Mid$("WC2A 2WT", 2, 1)
C

See the 'Mid Function' help topic for details.

--
Brendan Reynolds
Access MVP


Wayne-I-M said:
Hi all

Whilst it's easy to find the left or right portion of a text string
Something: Left([CD1stName],1) - 1st from left
Something: Right([CD1stName],1) - 1st from right
or even
Something: Left([CD1stName],2) - the 1st 2 diggits
Something: Right([CD1stName],17) - the last 17 digits
or whatever

Anyone any ideas how to seperate a text string so that I can find the
3rd
digit (number or text) or the 5th or even the 23rd, etc.

example
Postcode (zipcode) = WC2A 2WT
(made up postcode in case you happen to live there)
I need a seperate report field to show C and another to show W, or T,
etc.

Basically I need a report to show each seperate digit

Many thanks

Oh this is due to the rather silly "new and improved" method the UK
post
office has for dealing with pre-paid recorded bulk mailings (we are
sending
out over 150,000). We have to seperate each section of the postcode.
Click to expand...
Click to expand...
Click to expand...
 
Thank you both (Albert and Brendon)

You pointed me in the right direction

MidFunction spaces produce a zero length string and on a report this will
not matter and it will simply print a space (if that makes sense) which will
keep the post office scanner happy.

Thanks again for your expertise



--
Wayne
Manchester, England.
Enjoy whatever it is you do


Wayne-I-M said:
Hi Brendon,

I did create a small fuction this morning but it seems to fall down on the
spaces between the diggits

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Brendan Reynolds said:
? Mid$("WC2A 2WT", 2, 1)
C

See the 'Mid Function' help topic for details.

--
Brendan Reynolds
Access MVP


Wayne-I-M said:
Hi all

Whilst it's easy to find the left or right portion of a text string
Something: Left([CD1stName],1) - 1st from left
Something: Right([CD1stName],1) - 1st from right
or even
Something: Left([CD1stName],2) - the 1st 2 diggits
Something: Right([CD1stName],17) - the last 17 digits
or whatever

Anyone any ideas how to seperate a text string so that I can find the 3rd
digit (number or text) or the 5th or even the 23rd, etc.

example
Postcode (zipcode) = WC2A 2WT
(made up postcode in case you happen to live there)
I need a seperate report field to show C and another to show W, or T, etc.

Basically I need a report to show each seperate digit

Many thanks

Oh this is due to the rather silly "new and improved" method the UK post
office has for dealing with pre-paid recorded bulk mailings (we are
sending
out over 150,000). We have to seperate each section of the postcode.
Click to expand...
Click to expand...
Click to expand...
 
If you need to get, let's say, 2nd character after the space, you can
use InStr to define space's position and then use it in Mid.

For example: Mid([CD1stName], InStr(1,[CD1stName]," ") + 2 ,1)
That is if you always have one and only one space.

There is also a useful function Split (see documentation), which splits
your string into array. Most likely you won't need it here, but keep it
in mind for future cases.

"""Wayne-I-M ÐÉÓÁÌ(Á):
"""
Thank you Albert

The problems are UK postcode are not the same length

ie.
Manchester M60 1AB
Outter London NE12 2ZX
Inner London SW3B 7TU
Belfast BT1 1AB

etc, etc, etc.

The only common string is that they all have a space then 3 diggits on the
right.

As you can see - slightly confusing


--
Wayne
Manchester, England.
Enjoy whatever it is you do


Albert D. Kallal said:
If the field is consistant, no blanks, and always the same length..then
simply use mid


Something: Mid([CD1stName],10,1) - start at 10 for one character
Click to expand...
Click to expand...
 
Thank you Brendan

I have just printed the report and it works fine.

Your MidFunction suggestion is what I have used to seperate the field -
[PostCode].

If only you could write me a module (so it would work anywhere) that would
get someone to make some coffee in this office then my life would be much
simpler.

Thanks again.
--
Wayne
Manchester, England.
Enjoy whatever it is you do


Brendan Reynolds said:
Assuming Access 2000 or later ...

? Mid$(Replace("W C 2 A 2 W T"," ",""), 2, 1)
C

Re the variable length issue ...

Public Sub TestSub()

Const TestString As String = "W C 2 A 2 W T"

Dim lngLoop As Long
For lngLoop = 1 To Len(Replace(TestString, " ", ""))
Debug.Print Mid$(Replace(TestString, " ", ""), lngLoop, 1)
Next lngLoop

End Sub

Result in the Immediate Window ...

testsub
W
C
2
A
2
W
T

BTW: This is just quick-and-dirty demo code. In a finished app, it would be
more efficient to call Replace before entering the loop and assign the
result to a variable, then use the variable within the loop, to avoid
multiple calls to Replace within the loop.

--
Brendan Reynolds
Access MVP

Wayne-I-M said:
Hi Brendon,

I did create a small fuction this morning but it seems to fall down on the
spaces between the diggits

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Brendan Reynolds said:
? Mid$("WC2A 2WT", 2, 1)
C

See the 'Mid Function' help topic for details.

--
Brendan Reynolds
Access MVP


Hi all

Whilst it's easy to find the left or right portion of a text string
Something: Left([CD1stName],1) - 1st from left
Something: Right([CD1stName],1) - 1st from right
or even
Something: Left([CD1stName],2) - the 1st 2 diggits
Something: Right([CD1stName],17) - the last 17 digits
or whatever

Anyone any ideas how to seperate a text string so that I can find the
3rd
digit (number or text) or the 5th or even the 23rd, etc.

example
Postcode (zipcode) = WC2A 2WT
(made up postcode in case you happen to live there)
I need a seperate report field to show C and another to show W, or T,
etc.

Basically I need a report to show each seperate digit

Many thanks

Oh this is due to the rather silly "new and improved" method the UK
post
office has for dealing with pre-paid recorded bulk mailings (we are
sending
out over 150,000). We have to seperate each section of the postcode.
Click to expand...
Click to expand...
Click to expand...
 
Here y' go! :-)

Option Compare Database
Option Explicit

Public g_ctlAgent As Object
Public g_charMerlin As Object

Public Sub LoadMerlin()

'Requires a reference to Microsoft Agent Control 2.0 (agentctl.dll).
'May also require download of character, text to speech engine,
'and/or SAPI 4.0 runtime support, all available at ...
'http://www.microsoft.com/msagent/downloads/user.asp

'Set the object variable for the Microsoft Agent ActiveX control.
Set g_ctlAgent = New Agent

'Establish a connection to Microsoft Agent.
g_ctlAgent.Connected = True

'Load the character animation data file from the default folder.
g_ctlAgent.Characters.Load CharacterID:="merlin", _
LoadKey:="merlin.acs"

'Set the object variable for the character.
Set g_charMerlin = g_ctlAgent.Characters("merlin")
g_charMerlin.Show

g_charMerlin.Speak "Hey! Someone get Wayne some coffee!"

End Sub

--
Brendan Reynolds
Access MVP

Wayne-I-M said:
Thank you Brendan

I have just printed the report and it works fine.

Your MidFunction suggestion is what I have used to seperate the field -
[PostCode].

If only you could write me a module (so it would work anywhere) that would
get someone to make some coffee in this office then my life would be much
simpler.

Thanks again.
--
Wayne
Manchester, England.
Enjoy whatever it is you do


Brendan Reynolds said:
Assuming Access 2000 or later ...

? Mid$(Replace("W C 2 A 2 W T"," ",""), 2, 1)
C

Re the variable length issue ...

Public Sub TestSub()

Const TestString As String = "W C 2 A 2 W T"

Dim lngLoop As Long
For lngLoop = 1 To Len(Replace(TestString, " ", ""))
Debug.Print Mid$(Replace(TestString, " ", ""), lngLoop, 1)
Next lngLoop

End Sub

Result in the Immediate Window ...

testsub
W
C
2
A
2
W
T

BTW: This is just quick-and-dirty demo code. In a finished app, it would
be
more efficient to call Replace before entering the loop and assign the
result to a variable, then use the variable within the loop, to avoid
multiple calls to Replace within the loop.

--
Brendan Reynolds
Access MVP

Wayne-I-M said:
Hi Brendon,

I did create a small fuction this morning but it seems to fall down on
the
spaces between the diggits

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

? Mid$("WC2A 2WT", 2, 1)
C

See the 'Mid Function' help topic for details.

--
Brendan Reynolds
Access MVP


Hi all

Whilst it's easy to find the left or right portion of a text string
Something: Left([CD1stName],1) - 1st from left
Something: Right([CD1stName],1) - 1st from right
or even
Something: Left([CD1stName],2) - the 1st 2 diggits
Something: Right([CD1stName],17) - the last 17 digits
or whatever

Anyone any ideas how to seperate a text string so that I can find
the
3rd
digit (number or text) or the 5th or even the 23rd, etc.

example
Postcode (zipcode) = WC2A 2WT
(made up postcode in case you happen to live there)
I need a seperate report field to show C and another to show W, or
T,
etc.

Basically I need a report to show each seperate digit

Many thanks

Oh this is due to the rather silly "new and improved" method the UK
post
office has for dealing with pre-paid recorded bulk mailings (we are
sending
out over 150,000). We have to seperate each section of the
postcode.
Click to expand...
Click to expand...
Click to expand...
 
Wayne-I-M said:
Thank you Albert

The problems are UK postcode are not the same length

ie.
Manchester M60 1AB
Outter London NE12 2ZX
Inner London SW3B 7TU
Belfast BT1 1AB
Click to expand...

Right then......

ok, so, to get the 2nd digit of the postal code, you can use

Something: Mid(split([CD1stName]," ")(1),2,1)
 
No - does not work. Nothing to do with your code just lazy people.

As we have what is possibly the last non-PC office in Europe, I think I will
try something in the Japanese Access version which involves bending the head
slightly forward and getting a good slapping if coffee is not made. Think
will try a Google search for this. Although the Hari-Kari if not good enough
may be slightly over the top (mind you if no biscuits are produced with the
coffee – ya never know).

--
Wayne
Manchester, England.
Enjoy whatever it is you do


Brendan Reynolds said:
Here y' go! :-)

Option Compare Database
Option Explicit

Public g_ctlAgent As Object
Public g_charMerlin As Object

Public Sub LoadMerlin()

'Requires a reference to Microsoft Agent Control 2.0 (agentctl.dll).
'May also require download of character, text to speech engine,
'and/or SAPI 4.0 runtime support, all available at ...
'http://www.microsoft.com/msagent/downloads/user.asp

'Set the object variable for the Microsoft Agent ActiveX control.
Set g_ctlAgent = New Agent

'Establish a connection to Microsoft Agent.
g_ctlAgent.Connected = True

'Load the character animation data file from the default folder.
g_ctlAgent.Characters.Load CharacterID:="merlin", _
LoadKey:="merlin.acs"

'Set the object variable for the character.
Set g_charMerlin = g_ctlAgent.Characters("merlin")
g_charMerlin.Show

g_charMerlin.Speak "Hey! Someone get Wayne some coffee!"

End Sub

--
Brendan Reynolds
Access MVP

Wayne-I-M said:
Thank you Brendan

I have just printed the report and it works fine.

Your MidFunction suggestion is what I have used to seperate the field -
[PostCode].

If only you could write me a module (so it would work anywhere) that would
get someone to make some coffee in this office then my life would be much
simpler.

Thanks again.
--
Wayne
Manchester, England.
Enjoy whatever it is you do


Brendan Reynolds said:
Assuming Access 2000 or later ...

? Mid$(Replace("W C 2 A 2 W T"," ",""), 2, 1)
C

Re the variable length issue ...

Public Sub TestSub()

Const TestString As String = "W C 2 A 2 W T"

Dim lngLoop As Long
For lngLoop = 1 To Len(Replace(TestString, " ", ""))
Debug.Print Mid$(Replace(TestString, " ", ""), lngLoop, 1)
Next lngLoop

End Sub

Result in the Immediate Window ...

testsub
W
C
2
A
2
W
T

BTW: This is just quick-and-dirty demo code. In a finished app, it would
be
more efficient to call Replace before entering the loop and assign the
result to a variable, then use the variable within the loop, to avoid
multiple calls to Replace within the loop.

--
Brendan Reynolds
Access MVP

Hi Brendon,

I did create a small fuction this morning but it seems to fall down on
the
spaces between the diggits

--
Wayne
Manchester, England.
Enjoy whatever it is you do


:

? Mid$("WC2A 2WT", 2, 1)
C

See the 'Mid Function' help topic for details.

--
Brendan Reynolds
Access MVP


Hi all

Whilst it's easy to find the left or right portion of a text string
Something: Left([CD1stName],1) - 1st from left
Something: Right([CD1stName],1) - 1st from right
or even
Something: Left([CD1stName],2) - the 1st 2 diggits
Something: Right([CD1stName],17) - the last 17 digits
or whatever

Anyone any ideas how to seperate a text string so that I can find
the
3rd
digit (number or text) or the 5th or even the 23rd, etc.

example
Postcode (zipcode) = WC2A 2WT
(made up postcode in case you happen to live there)
I need a seperate report field to show C and another to show W, or
T,
etc.

Basically I need a report to show each seperate digit

Many thanks

Oh this is due to the rather silly "new and improved" method the UK
post
office has for dealing with pre-paid recorded bulk mailings (we are
sending
out over 150,000). We have to seperate each section of the
postcode.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top