split cell in number-cell and letter-cell

  • Thread starter Thread starter ppeer
  • Start date Start date
P

ppeer

Hi Expert,

I would like a loop-routine to split out (letter and number) cells in
a letter and a number cell, like:
(It has to be implemented by a vba routine, no worksheet formula)
The first part are always letters, the last part numbers.

input: output after routine:
cellColumnA: cellColumnB: cellColumnC:
peterpan 89772 peterpan 89772

Please, could someone help me out?

best regards,
Peter
 
select yr range of cells to split and use this

Sub cus()
Dim cell As Range

For Each cell In Selection
On Error Resume Next
For i = 1 To Len(ActiveCell.Value)
If Not IsNumeric(Mid(ActiveCell.Value, i, 1) * 1) Then
GoTo follow
Else
Exit For
End If
follow:
Next i

ActiveCell.Offset(0, 1) = Mid(ActiveCell.Value, 1, i - 1)
ActiveCell.Offset(0, 2) = Mid(ActiveCell.Value, i, Len
(ActiveCell.Value) - i + 1)

Next cell

End Sub

HIH
 
select yr range of cells to split and use this

Sub cus()
Dim cell As Range

For Each cell In Selection
On Error Resume Next
For i = 1 To Len(ActiveCell.Value)
If Not IsNumeric(Mid(ActiveCell.Value, i, 1) * 1) Then
GoTo follow
Else
Exit For
End If
follow:
Next i

ActiveCell.Offset(0, 1) = Mid(ActiveCell.Value, 1, i - 1)
ActiveCell.Offset(0, 2) = Mid(ActiveCell.Value, i, Len
(ActiveCell.Value) - i + 1)

Next cell

End Sub

HIH







- Tekst uit oorspronkelijk bericht weergeven
Click to expand...

Works Perfectly!!! Thanks a lot...
 
in yr example you'll be left with "peterpan "
if you prefer to have no trailing spaces change

ActiveCell.Offset(0, 1) = Mid(ActiveCell.Value, 1, i - 1)

to

ActiveCell.Offset(0, 1) = Mid(ActiveCell.Value, 1, i - 2)

or use Edit->Replace to get rid of them

pls click YES if it helped
 
Back
Top