Excel When numerals and text are found in the same column move numerals to left cell

Question

Excel When numerals and text are found in the same column move numerals to left cell

SteveD 495

Hello from Steve

When numerals and text are found in the same column move numerals to left cell please.

Sub HighlightCellsWithNumbersAndText()

Dim ws As Worksheet

Dim rng As Range, cell As Range

Dim hasNumber As Boolean, hasLetter As Boolean

Dim i As Long

' Use the active sheet in the active workbook

Set ws = ActiveWorkbook.ActiveSheet

Set rng = ws.Range("C1:C250")

' Loop through each cell in the range

For Each cell In rng

    If VarType(cell.Value) = vbString Or _

       VarType(cell.Value) = vbDouble Or _

       VarType(cell.Value) = vbVariant Then

        hasNumber = False

        hasLetter = False

        ' Check each character in the cell

        For i = 1 To Len(cell.Text)

            If Mid(cell.Text, i, 1) Like "[0-9]" Then hasNumber = True

            If Mid(cell.Text, i, 1) Like "[A-Za-z]" Then hasLetter = True

            ' Exit early if both found

            If hasNumber And hasLetter Then Exit For

        Next i

        ' Highlight if both number and letter exist

        If hasNumber And hasLetter Then

            cell.Interior.Color = RGB(255, 255, 0) ' Yellow highlight

        End If

    End If

Next cell

MsgBox "Highlighting complete.", vbInformation

End Sub

0 comments

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Use the existing logic that detects cells containing both numbers and letters, and when such a cell is found, split the numeric and text parts and move the numeric part to the cell on the left.

Example macro based on the provided code:

Sub MoveNumbersToLeftCell()

    Dim ws As Worksheet
    Dim rng As Range, cell As Range
    Dim hasNumber As Boolean, hasLetter As Boolean
    Dim i As Long
    Dim ch As String
    Dim numPart As String
    Dim textPart As String

    Set ws = ActiveWorkbook.ActiveSheet
    Set rng = ws.Range("C1:C250")

    For Each cell In rng
        If VarType(cell.Value) = vbString Or _
           VarType(cell.Value) = vbDouble Or _
           VarType(cell.Value) = vbVariant Then

            hasNumber = False
            hasLetter = False

            ' Check each character in the cell
            For i = 1 To Len(cell.Text)
                ch = Mid(cell.Text, i, 1)
                If ch Like "[0-9]" Then hasNumber = True
                If ch Like "[A-Za-z]" Then hasLetter = True
                If hasNumber And hasLetter Then Exit For
            Next i

            ' If both number and letter exist, split and move
            If hasNumber And hasLetter Then

                numPart = ""
                textPart = ""

                ' Build numeric and text parts
                For i = 1 To Len(cell.Text)
                    ch = Mid(cell.Text, i, 1)
                    If ch Like "[0-9]" Then
                        numPart = numPart & ch
                    Else
                        textPart = textPart & ch
                    End If
                Next i

                ' Trim text part
                textPart = Trim(textPart)

                ' Put numeric part in left cell, text part stays in current cell
                If numPart <> "" Then
                    cell.Offset(0, -1).Value = numPart
                End If

                cell.Value = textPart

            End If
        End If
    Next cell

    MsgBox "Processing complete.", vbInformation

End Sub

This macro:

Scans C1:C250.
Detects cells containing both digits and letters.
Splits digits into numPart and non-digit characters into textPart.
Writes numPart into the cell to the left (column B) and leaves textPart in column C.

Adjust Range("C1:C250") and Offset(0, -1) as needed for different columns.

References:

Share via

Excel When numerals and text are found in the same column move numerals to left cell

0 additional answers

Your answer