how to seperate digit from string.

[Guoqi Zheng]

Sorry, I clicked wrong to send out a unfinished post a moment ago.

I have a table with the followings rows.

90CAP
500ML
VERP
100ML
100ML
20ST
20ST
20ASS
20ST
92G
...


I need to seperate them into two columns, one contains the digits, one got
the letters. For example, 20St, I need to seperate them into one column with
20 and the other one with ST on it.

How can I do this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

 

[Lynn Trapp]

Give this a try:

SELECT IIF(Val([YourField]) = 0, Null, Val([YourField])) AS NumericPart,
Mid([YourField],Len(Val([YourField]))+1) AS TextPart
FROM YourTable;

 

[Peter Frey]

Hi Lynn,

but if one of the values were 333d3000, you'd get an error. Do you know a
solution for this problem, too?

Thanks a lot, Peter

Give this a try:

SELECT IIF(Val([YourField]) = 0, Null, Val([YourField])) AS
NumericPart, Mid([YourField],Len(Val([YourField]))+1) AS TextPart
FROM YourTable;

Sorry, I clicked wrong to send out a unfinished post a moment ago.

I have a table with the followings rows.

90CAP
500ML
VERP
100ML
100ML
20ST
20ST
20ASS
20ST
92G
...


I need to seperate them into two columns, one contains the digits,
one got the letters. For example, 20St, I need to seperate them into
one column with 20 and the other one with ST on it.

How can I do this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Mit freundlichen Grüßen

Peter Frey, DATEX GmbH
Am Sandfeld 17a - 76149 Karlsruhe
Tel. 0721-7838060, Fax 0721-7838033
(e-mail address removed) http://www.datex.de

 

[Lynn Trapp]

You'll have to check for the presence of the letter "D" in the string,
because Val() is evaluating that as a Decimal value. This should get you on
the right track:


IIf(InStr([YourField],"D"),Left([YourField],InStr([YourField],"D")-1),Val([Y
ourField]))

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi Lynn,

but if one of the values were 333d3000, you'd get an error. Do you know a
solution for this problem, too?

Thanks a lot, Peter

Give this a try:

SELECT IIF(Val([YourField]) = 0, Null, Val([YourField])) AS
NumericPart, Mid([YourField],Len(Val([YourField]))+1) AS TextPart
FROM YourTable;

Sorry, I clicked wrong to send out a unfinished post a moment ago.

I have a table with the followings rows.

90CAP
500ML
VERP
100ML
100ML
20ST
20ST
20ASS
20ST
92G
...


I need to seperate them into two columns, one contains the digits,
one got the letters. For example, 20St, I need to seperate them into
one column with 20 and the other one with ST on it.

How can I do this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Mit freundlichen Grüßen

Peter Frey, DATEX GmbH
Am Sandfeld 17a - 76149 Karlsruhe
Tel. 0721-7838060, Fax 0721-7838033
(e-mail address removed) http://www.datex.de

 

[Peter Frey]

Hi Lynn,

thank you, I'll give it a try.

You'll have to check for the presence of the letter "D" in the string,
because Val() is evaluating that as a Decimal value. This should get
you on the right track:


IIf(InStr([YourField],"D"),Left([YourField],InStr([YourField],"D")-1),Val([Y
ourField]))

Hi Lynn,

but if one of the values were 333d3000, you'd get an error. Do you
know a solution for this problem, too?

Thanks a lot, Peter

Give this a try:

SELECT IIF(Val([YourField]) = 0, Null, Val([YourField])) AS
NumericPart, Mid([YourField],Len(Val([YourField]))+1) AS TextPart
FROM YourTable;


Sorry, I clicked wrong to send out a unfinished post a moment ago.

I have a table with the followings rows.

90CAP
500ML
VERP
100ML
100ML
20ST
20ST
20ASS
20ST
92G
...


I need to seperate them into two columns, one contains the digits,
one got the letters. For example, 20St, I need to seperate them
into one column with 20 and the other one with ST on it.

How can I do this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Mit freundlichen Grüßen

Peter Frey, DATEX GmbH
Am Sandfeld 17a - 76149 Karlsruhe
Tel. 0721-7838060, Fax 0721-7838033
(e-mail address removed) http://www.datex.de

 

[Guest]

Sorry, I clicked wrong to send out a unfinished post a moment ago.

I have a table with the followings rows.

90CAP
500ML
VERP
100ML
100ML
20ST
20ST
20ASS
20ST
92G
...

I need to seperate them into two columns, one contains the digits, one got
the letters. For example, 20St, I need to seperate them into one column with
20 and the other one with ST on it.

How can I do this?

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

--
Kind regards

Guoqi Zheng
guoqi AT meetholland dot com
Http://www.meetholland.com

This is written in VB.net, but it should translate easily to VBA. You may have
to tweak it a little for you needs but it should send you in the right
direction. Put the two functions in a module, then you can include SplitAlpha
and SplitNumeric in a query based on the original table as two columns.
gm

Function SplitAlpha(ByVal Input As String) As String
Dim strStringPart As String

If IsNumeric(Input) Then
strStringPart = "" 'Alpha part is empty string for numeric string
Else
Dim StringLength As Integer
StringLength = Len(Input)

Dim intN As Integer
intN = 1

Do While IsNumeric(Mid(Input, intN, 1)) = False And _
intN <= StringLength
strStringPart &= Mid(Input, intN, 1)
intN += 1
Loop
End If
Return strStringPart 'Return Text Portion
End Function


Function SplitNumeric(ByVal Input As String) As String
Dim strStringPart As String

If IsNumeric(Input) Then
strStringPart = Input 'Alpha part is empty string for numeric string

Else
Dim StringLength As Integer
StringLength = Len(Input)

Dim intN As Integer
intN = 1

Do Until intN > StringLength
If IsNumeric(Mid(Input, intN, 1)) = True Then
strStringPart &= Mid(Input, intN, 1)
End If
intN += 1
Loop
End If
'Return Numeric Portion
'Note: still a string at this point.
Return strStringPart
End Function