# Thread: Excel Programming

1. Registered User
Join Date
Feb 2004
Posts
3

## Unanswered: Excel Programming

Suppose Cell A1 has "TWID10293 Comments" entry and
cell A2 has "TW298382 Comment on this." entry.
I wish to retrieve the numeric part of A1 and A2 in B1 and B2 respectively. ie B1 should have "10293" and B2 should have "29382".

2. Registered User
Join Date
Oct 2003
Posts
17

## Re: Excel Programming

Originally posted by Rajneesh
Suppose Cell A1 has "TWID10293 Comments" entry and
cell A2 has "TW298382 Comment on this." entry.
I wish to retrieve the numeric part of A1 and A2 in B1 and B2 respectively. ie B1 should have "10293" and B2 should have "29382".
You will need to create a public function in some module in your xls.

Public Function FindNumPart(aStr As String) As String
'
' Find continuous numeric part from the given string
Dim i As Integer
Dim sResult As String
Dim bNumFound As Boolean
bNumFound = False
sResult = ""

For i = 1 To Len(aStr)
If IsNumeric(Mid(aStr, i, 1)) Then
bNumFound = True
sResult = sResult & Mid(aStr, i, 1)
Else
If bNumFound Then
Exit For
End If
End If
Next
FindNumPart = sResult
End Function

In the excel sheet cell (for example b1) you will need to put
=IF(ISERROR(VALUE(FindNumPart(A1))),"", VALUE(FindNumPart(A1)))

Here I have assumed that there is only one and first continous number within the string, that you are interested in. The for loop is exited when non numeric character after the number is found.

(For the second row, copy the same formula in the cell b2)

HTH

3. Registered User
Join Date
Feb 2004
Posts
3

## Re: Excel Programming

Thanks a lot.

Originally posted by jparagg
You will need to create a public function in some module in your xls.

Public Function FindNumPart(aStr As String) As String
'
' Find continuous numeric part from the given string
Dim i As Integer
Dim sResult As String
Dim bNumFound As Boolean
bNumFound = False
sResult = ""

For i = 1 To Len(aStr)
If IsNumeric(Mid(aStr, i, 1)) Then
bNumFound = True
sResult = sResult & Mid(aStr, i, 1)
Else
If bNumFound Then
Exit For
End If
End If
Next
FindNumPart = sResult
End Function

In the excel sheet cell (for example b1) you will need to put
=IF(ISERROR(VALUE(FindNumPart(A1))),"", VALUE(FindNumPart(A1)))

Here I have assumed that there is only one and first continous number within the string, that you are interested in. The for loop is exited when non numeric character after the number is found.

(For the second row, copy the same formula in the cell b2)

HTH

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•