Moving Character to next cell

[moone009]

I am building an excel address parser.

Column B = streetnumber
Column C = UnitNumber

Column B is my streetNumber and What I need it to do is anytime it finds a letter attached to the number it needs to move it from column b to column C.

So if it ended up on B435 how do I get that value of "A" to be on C435

Try
                If (xlWorkSheet.Range("b2:b25000").Find(What:="a")).Count > 0 Then
                    xlWorkSheet.Cells.Replace(What:="a", Replacement:="")
                End If
            Catch ex As Exception

[lolafuertes]

Unfortunatelly there is no magic ReplaceInTheCurrentCellMovingTheReplacedPartToAnotherCell function in Excel.

I would suggest to do a For - Next loop taking one cell at time in the search range.
Then in the taken cell, define a new text value to set and, for each character in the cell, if the current character is not between "0"c and "9"c, add this character to the destination cell, else add it to the new text value.
After ending the for each, move the new text value to the current cell.

Please let us know if this approach is working for you.

Hpe this helps

[Reverend Jim]

This is a starting point. You can modify it, perhaps, to start at row one and continue until it finds an empty cell. To test, populate the first four cells of column B with your test addresses then run the macro (I assigned it to CTRL-M but you can just run it from the menu).

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2011-09-16 by Jim
'
' Keyboard Shortcut: Ctrl+m
'
    Dim cell As String
    
    With ActiveSheet

        For i = 1 To 4
            cell = .Cells(i, 2)
            If Not IsNumeric(Left(cell, 1)) Then
                .Cells(i, 3) = Left(cell, 1)
                .Cells(i, 2) = Mid(cell, 2)
            End If
        Next
    
    End With
    
End Sub

[moone009]

I tried this method but still no luck.

Dim S As Integer
        For S = 1 To 10
            Try
                If (xlWorkSheet.Range("b" & S).Value = "a") Then
                    xlWorkSheet.Range("C" & S).Value = "a"
                 
                End If
            Catch ex As Exception
            End Try
        Next

[moone009]

I also tried this but then it it fills my entire column in C

Dim S As Integer
        For S = 1 To 10
            Try
                If (xlWorkSheet.Range("b" & S).Find(What:="A")).Count > 0 Then

                    xlWorkSheet.Range("C" & S).Value = "a"

                End If
            Catch ex As Exception
            End Try

        Next

[Reverend Jim]

anytime it finds a letter attached to the number it needs to move it from column b to column C.

I believe my code does exactly what you said you wanted. You said move it to column C. If you don't want it to replace column C then concatenate it to the value that is already there.