Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts

    separating numeric and text in a column

    I have data in columns in which some cells contain numbers only and some are numbers and text as shown in the attached. I would like to insert a column and move the text to the right of the numbers. The text is inconsistent with spaces, so a text to columns doesn't work. Any suggestions?

    Thanks.
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    I've written a Macro, ExtractText, which - as an example - takes the values in Column J, Rows 3 to 21 (I've named this range MyRange) and processes them in the way you describe, putting the numeric value into column Q and the text into column R:

    Code:
    Sub ExtractText()
    '
    Dim Cell As Range
    Dim Number As String
    Dim Text As String
    Dim Character As String
    Dim n As Integer
    '
    For Each Cell In Range("MyRange")
        Number = ""
        Text = ""
        For n = 1 To Len(Cell.Value)
            Character = Mid(Cell.Value, n, 1)
            Select Case Character
                Case 0 To 9
                    Number = Number & Character
                Case "."
                    Number = Number & Character
                Case Else
                    Text = Text & Character
            End Select
        Next n
        Cell.Offset(0, 7).Value = Number
        Cell.Offset(0, 8).Value = Text
    Next Cell
    '
    End Sub
    If this is the outcome you want, are you able to adapt it to your precise needs ?

    Adapted Workbook attached as an Excel 2003 file.
    Attached Files Attached Files

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 106 Times in 91 Posts
    If you just wanted to get rid of the text elements in columns I to O, there's a tweaked v2 of your file attached as v3.
    Attached Files Attached Files
    Last edited by MartinM; 2013-12-18 at 17:01.

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    JE,

    Well Martin is faster than I but here's another approach. You select the column you want to separate and then run this macro.
    Code:
    Option Explicit
    
    Sub Macro1()
    
       Dim lCurRow  As Long
       Dim lLastRow As Long
       Dim lSep     As Long
       Dim iColNo   As Integer
       
       Application.ScreenUpdating = False
       
       With Selection
           .Offset(0, 1).Insert Shift:=xlToRight
           iColNo = .Column
       End With  'Selection
       
       lLastRow = Cells(Rows.Count, iColNo).End(xlUp).Row()
       
       For lCurRow = 3 To lLastRow
          On Error Resume Next
          lSep = InStr(1, Cells(lCurRow, iColNo).Value, " ")
          On Error GoTo 0
          If lSep > 0 Then
            Cells(lCurRow, iColNo + 1).Value = _
                  Right(Cells(lCurRow, iColNo).Value, _
                  Len(Cells(lCurRow, iColNo).Value) - lSep)
            Cells(lCurRow, iColNo).Value = Left(Cells(lCurRow, iColNo).Value, (lSep - 1))
          Else
          '*** Handles case where only text in cell ***
            If Len(Cells(lCurRow, iColNo).Value) > 0 And _
               Val(Cells(lCurRow, iColNo)) = 0 Then
              Cells(lCurRow, iColNo + 1).Value = Cells(lCurRow, iColNo).Value
              Cells(lCurRow, iColNo).Value = ""
            End If
          End If
       Next lCurRow
       
    End Sub
    Results:
    jepalmer.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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