i want to seprate the number from the sentence.
in A1 i have a sentence . anil somchand uzenwal. vartak nagar 258932.
in a2 i have sentence . sunil 34521 vartak nagar .

i want a result in cell B1 2588932
in cell B2 34521

so pls help me . i have a lot of data . i want to seprate th all number from
th sentence. the number can be in middle or starting or the end of the
sentence.

so pls help.

Try Tom Ogilvy's code:

Public Function ReturnNumerals(rng As Range) As String
Dim sStr As String, i As Long, sStr1 As String
Dim sChar As String
sStr = rng.Value
For i = 1 To Len(sStr)
sChar = Mid(sStr, i, 1)
If sChar Like "[0-9]" Then
sStr1 = sStr1 & sChar
End If
Next
' Debug.Print sStr, sStr1
ReturnNumerals = sStr1
End Function



--
Gary's Student


"hitesh" wrote:

i want to seprate the number from the sentence.
in A1 i have a sentence . anil somchand uzenwal. vartak nagar 258932.
in a2 i have sentence . sunil 34521 vartak nagar .

i want a result in cell B1 2588932
in cell B2 34521

so pls help me . i have a lot of data . i want to seprate th all number from
th sentence. the number can be in middle or starting or the end of the
sentence.

so pls help.

Can you make VB macro, which splits up string
by Strings.split(input," ") function, array is created in result.
and then go through all elements in a loop and checks if data type is
numeric .

than you can call macro as usual excel function!

A.


"hitesh" wrote in message
...
i want to seprate the number from the sentence.
in A1 i have a sentence . anil somchand uzenwal. vartak nagar 258932.
in a2 i have sentence . sunil 34521 vartak nagar .

i want a result in cell B1 2588932
in cell B2 34521

so pls help me . i have a lot of data . i want to seprate th all number
from
th sentence. the number can be in middle or starting or the end of the
sentence.

so pls help.

A macro to delete all but numerics.

If you wish to retain the source cells, make a copy of the data in an adjacent
column and run the macro on that column.

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points and hyphens.
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
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 Tue, 14 Nov 2006 16:30:39 +0200, "ciruliz" wrote:

Can you make VB macro, which splits up string
by Strings.split(input," ") function, array is created in result.
and then go through all elements in a loop and checks if data type is
numeric .

than you can call macro as usual excel function!

A.


"hitesh" wrote in message
...
i want to seprate the number from the sentence.
in A1 i have a sentence . anil somchand uzenwal. vartak nagar 258932.
in a2 i have sentence . sunil 34521 vartak nagar .

i want a result in cell B1 2588932
in cell B2 34521

so pls help me . i have a lot of data . i want to seprate th all number
from
th sentence. the number can be in middle or starting or the end of the
sentence.

so pls help.

i dont know how to make macro as iam new. so pls explain me step by step.i
think its in visul basic. pls help

"Gord Dibben" wrote:

A macro to delete all but numerics.

If you wish to retain the source cells, make a copy of the data in an adjacent
column and run the macro on that column.

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points and hyphens.
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
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 Tue, 14 Nov 2006 16:30:39 +0200, "ciruliz" wrote:

Can you make VB macro, which splits up string
by Strings.split(input," ") function, array is created in result.
and then go through all elements in a loop and checks if data type is
numeric .

than you can call macro as usual excel function!

A.


"hitesh" wrote in message
...
i want to seprate the number from the sentence.
in A1 i have a sentence . anil somchand uzenwal. vartak nagar 258932.
in a2 i have sentence . sunil 34521 vartak nagar .

i want a result in cell B1 2588932
in cell B2 34521

so pls help me . i have a lot of data . i want to seprate th all number
from
th sentence. the number can be in middle or starting or the end of the
sentence.

so pls help.

In article ,
hitesh wrote:

i dont know how to make macro as iam new. so pls explain me step by step.i
think its in visul basic. pls help

See

http://www.mvps.org/dmcritchie/excel/getstarted.htm

hitesh

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + r to open Project Explorer.

Find your workbook/project and select it.

Right-click and InsertModule. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

Run the macro by going to ToolMacroMacros.

You can also assign this macro to a button or a shortcut key combo.


Gord

On Sun, 19 Nov 2006 05:27:01 -0800, hitesh
wrote:

i dont know how to make macro as iam new. so pls explain me step by step.i
think its in visul basic. pls help

"Gord Dibben" wrote:

A macro to delete all but numerics.

If you wish to retain the source cells, make a copy of the data in an adjacent
column and run the macro on that column.

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points and hyphens.
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
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 Tue, 14 Nov 2006 16:30:39 +0200, "ciruliz" wrote:

Can you make VB macro, which splits up string
by Strings.split(input," ") function, array is created in result.
and then go through all elements in a loop and checks if data type is
numeric .

than you can call macro as usual excel function!

A.


"hitesh" wrote in message
...
i want to seprate the number from the sentence.
in A1 i have a sentence . anil somchand uzenwal. vartak nagar 258932.
in a2 i have sentence . sunil 34521 vartak nagar .

i want a result in cell B1 2588932
in cell B2 34521

so pls help me . i have a lot of data . i want to seprate th all number
from
th sentence. the number can be in middle or starting or the end of the
sentence.

so pls help.




Gord Dibben MS Excel MVP