Microsoft Excel - want to separate text and numeric value from one cell

Asked By sohrab
07-Sep-10 05:56 AM
Hi
can any one help me in separating text and numeric valsu from the same cell.eg

2154neerah
u6jhlfnchss
uydhjdgf001
lk544ssdfli

i want that numeric value should be separated from and it should be show me in the next cell.


Rgds
Sohrab
  R VENKAT replied to sohrab
07-Sep-10 07:52 AM
the data is from A1 to A4
 try this macro 

Sub test()
Dim j As String, k As Long, m As Long, r As Range, c As Range
Set r = Range(Range("A1"), Range("a1").End(xlDown))
Columns("B:B").Cells.Clear
j = 0
For Each c In r
k = Len(c)
For m = 1 To k
If IsNumeric(Mid(c, m, 1)) Then
j = j & Mid(c, m, 1)
End If
Next m
j = Right(j, Len(j) - 1)
MsgBox j
c.Offset(0, 1).NumberFormat = "@"
c.Offset(0, 1) = j
j = 0
Next c
End Sub

  Rolf Jaeger replied to sohrab
07-Sep-10 02:41 PM
Hi Sohrab:

here is a slightly simplified version of the code submitted by R Venkat.

Sub ExtractNumbers()
  Dim j As String, k As Long, m As Long
  Dim r As Range, c As Range
  Set r = ActiveSheet.UsedRange.Columns("A")
  Columns("B:B").Cells.Clear
  For Each c In r.Cells
    j = ""
    For m = 1 To Len(c.Value)
      If IsNumeric(Mid(c, m, 1)) Then j = j & Mid(c, m, 1)
    Next m
    c.Offset(0, 1).Value = Conversion.CInt(j)
  Next c
End Sub

Best wishes,
Rolf
Create New Account
help
Microsoft Excel Microsoft Excel (full name Microsoft Office Excel ) is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X . It features calculation, graphing tools, pivot tables and a macro
Microsoft Excel Microsoft Excel (full name Microsoft Office Excel ) is a spreadsheet application written and distributed by Microsoft for Microsoft Windows and Mac OS X . It features calculation, graphing tools, pivot tables and a macro
Macro Excel Excel Vorrei convertire macro excel in openoffice cal grazie Excel - Italian Discussions Excel (1) Macro (1) Excel Vorrei (1) Database (1) Informazioni (1) Il messaggio (1) Gamberini (1) Microsoft (1
CHANGE CASE COMMAND IN MICROSOFT EXCEL Excel Microsoft Word contains the command of CHANGE CASE which is very useful, but Microsoft Excel does not contain this command due to which its too much difficult as rewrite the whole content of a cell. Cant this command be available in Microsoft Excel too, I think its not hard for Microsoft Corporation. Or if this command is available
Opening / Closing files using Excel Macro? Excel Excel 2007 on XP Pro: I am trying to write a a macro in Excel which will: (1) open a workbook from another only if not already open (2) close the workbook from another only if the other workbook is already open. Any ideas? Excel Programming Discussions MsoAutomationSecurityByUI (1) Excel 2007 (1) Error (1) Application.AutomationSecurity (1) Excel (1) Application.FileDialog (1) MsoFileDialogFilePicker (1) Workbook