Given data like the following:

B.3.54
A.14.1
BC.323.88493
ABC.1.4993

I need to always extract the first value after the first dot (reading left
to right). So, continuing in the example shown, I need to get: 3; 14; 323;
and 1;

This runs on one line at a time so I need to be able to just get one value
at a time copied into a variable. Can someone please post example code that
can do this?

Thanks much in advance.

The function below will accept a string and return the value between the
first and second dots. If there are less than 2 dots in the string or if the
value between the first 2 dots is not numeric, the function will return -1.

Public Function GetFirstNumber(strText As String) As Long
Dim dotPos1 As Integer
Dim dotPos2 As Integer
Dim strTemp As String
GetFirstNumber = -1
dotPos1 = InStr(strText, ".")
dotPos2 = InStr(dotPos1 + 1, strText, ".")
If dotPos1 0 And dotPos2 0 Then
strTemp = Mid(strText, dotPos1 + 1, dotPos2 - dotPos1 - 1)
If IsNumeric(strTemp) Then
GetFirstNumber = CLng(strTemp)
End If
End If
End Function


--

Hope that helps.

Vergel Adriano


"XP" wrote:


Given data like the following:

B.3.54
A.14.1
BC.323.88493
ABC.1.4993

I need to always extract the first value after the first dot (reading left
to right). So, continuing in the example shown, I need to get: 3; 14; 323;
and 1;

This runs on one line at a time so I need to be able to just get one value
at a time copied into a variable. Can someone please post example code that
can do this?

Thanks much in advance.

Hi
Public function ExtractIt(myString as Variant) as Long
TempString = ""
'delete bit up to and including first dot
TempString = Right(Cstr(myString),len(myString)-Instr(myString,"."))
'extract up to second dot. assuming there is one
ExtractIt = Val(Left(TempString, Instr(TempString,"."))
end function

Sub tester()
For each cell in Range("A1:A100")
myVariable = ExtractIt(Cell.Value)
msgbox myVariable
next cell
end sub


regards
Paul

On Mar 20, 3:14 pm, XP wrote:
Given data like the following:

B.3.54
A.14.1
BC.323.88493
ABC.1.4993

I need to always extract the first value after the first dot (reading left
to right). So, continuing in the example shown, I need to get: 3; 14; 323;
and 1;

This runs on one line at a time so I need to be able to just get one value
at a time copied into a variable. Can someone please post example code that
can do this?

Thanks much in advance.

Hi
Just seen Vergel's code - much better!
Paul

On Mar 20, 3:41 pm, wrote:
Hi
Public function ExtractIt(myString as Variant) as Long
TempString = ""
'delete bit up to and including first dot
TempString = Right(Cstr(myString),len(myString)-Instr(myString,"."))
'extract up to second dot. assuming there is one
ExtractIt = Val(Left(TempString, Instr(TempString,"."))
end function

Sub tester()
For each cell in Range("A1:A100")
myVariable = ExtractIt(Cell.Value)
msgbox myVariable
next cell
end sub

regards
Paul

On Mar 20, 3:14 pm, XP wrote:



Given data like the following:

B.3.54
A.14.1
BC.323.88493
ABC.1.4993

I need to always extract the first value after the first dot (reading left
to right). So, continuing in the example shown, I need to get: 3; 14; 323;
and 1;

This runs on one line at a time so I need to be able to just get one value
at a time copied into a variable. Can someone please post example code that
can do this?

Thanks much in advance.- Hide quoted text -

- Show quoted text -

I didn't know if you wanted a string or a number returned, so I returned a
string
Use custom function below

Call with
=Get2ndString(A1)

Strings that don't have two periods will give errors

where A1 is any cell containing a string

Function Get2ndString(Cell As Range) As String

'get first period
Period = InStr(Cell, ".")
'get string after 1st period
Get2ndString = Mid(Cell, Period + 1)
'get second period
Period = InStr(Get2ndString, ".")
Get2ndString = Left(Get2ndString, Period - 1)

End Function

"XP" wrote:


Given data like the following:

B.3.54
A.14.1
BC.323.88493
ABC.1.4993

I need to always extract the first value after the first dot (reading left
to right). So, continuing in the example shown, I need to get: 3; 14; 323;
and 1;

This runs on one line at a time so I need to be able to just get one value
at a time copied into a variable. Can someone please post example code that
can do this?

Thanks much in advance.

On Tue, 20 Mar 2007 08:14:35 -0700, XP wrote:


Given data like the following:

B.3.54
A.14.1
BC.323.88493
ABC.1.4993

I need to always extract the first value after the first dot (reading left
to right). So, continuing in the example shown, I need to get: 3; 14; 323;
and 1;

This runs on one line at a time so I need to be able to just get one value
at a time copied into a variable. Can someone please post example code that
can do this?

Thanks much in advance.


===============================
Option Explicit
Sub Get2nd()
Dim i As Long
Dim sStr As Variant
Dim oRegExp As Object
Dim colMatches As Object
Set oRegExp = CreateObject("VBScript.RegExp")

sStr = Array("B.3.54", "A.14.1", "BC.323.88493", "ABC.1.4993")

With oRegExp
.IgnoreCase = True
.Global = True
.Pattern = "(\w+\.)(\d+)"
For i = 0 To UBound(sStr)
If .test(sStr(i)) = True Then
Set colMatches = .Execute(sStr(i))
Debug.Print i, colMatches(0).submatches(1)
End If
Next i
End With
End Sub
==================================
--ron