Please help with removing text string

For example I have data MK12345 and would like to remove the text from that so I only have the numerica value return.

What VBA code do I use?

PS: Is there a list somehwere which will list description of all the functions available?


Thank you!

Paste this code into a Standard Module of your Workbook:

Public Function GetNumPart(c) As String
Dim i As Integer
Dim MyString As String

'Returning numeric value from string'
MyString = ""
For i = 1 To Len(c)
If InStr(1, "0123456789", Mid(c, i, 1), vbTextCompare) > 0 Then
MyString = MyString + Mid(c, i, 1)
End If
Next i
GetNumPart = MyString
End Function

Then at a sheet
Example, if in cell A1 you had: Zorgia [NBK-R] (-89,113) Defending
In B1 enter =GetNumPart(A1)

B1 will (should) produce 89113



"excelator" wrote:

>
> For example I have data MK12345 and would like to remove the text from
> that so I only have the numerica value return.
>
> What VBA code do I use?
>
> PS: Is there a list somehwere which will list description of all the
> functions available?
>
>
> Thank you!
>
>
> --
> excelator
> ------------------------------------------------------------------------
> excelator's Profile: http://www.excelforum.com/member.php...o&userid=37504
> View this thread: http://www.excelforum.com/showthread...hreadid=571567
>
>

ASAP Utilities, a free Add-in available at www.asap-utilities.com has a
feature that will remove all the alpha-characters from a string leaving only
the numbers............

Vaya con Dios,
Chuck, CABGx3


"excelator" <excelator.2cjtvu_1155590714.7286@excelforum-nospam.com> wrote
in message news:excelator.2cjtvu_1155590714.7286@excelforum-nospam.com...
>
> For example I have data MK12345 and would like to remove the text from
> that so I only have the numerica value return.
>
> What VBA code do I use?
>
> PS: Is there a list somehwere which will list description of all the
> functions available?
>
>
> Thank you!
>
>
> --
> excelator
> ------------------------------------------------------------------------
> excelator's Profile:
http://www.excelforum.com/member.php...o&userid=37504
> View this thread: http://www.excelforum.com/showthread...hreadid=571567
>

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points.
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String
Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9.]" Then
' adjust to [0-9] if do not want decimal pts.
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR
End Sub


Gord Dibben MS Excel MVP

On Mon, 14 Aug 2006 17:20:53 -0400, excelator
<excelator.2cjtvu_1155590714.7286@excelforum-nospam.com> wrote:

>
>For example I have data MK12345 and would like to remove the text from
>that so I only have the numerica value return.
>
>What VBA code do I use?
>
>PS: Is there a list somehwere which will list description of all the
>functions available?
>
>
>Thank you!