split cell in number-cell and letter-cell

[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

 

[Jarek Kujawa]

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

 

[ppeer]

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

Works Perfectly!!! Thanks a lot...

 

[Jarek Kujawa]

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

 

[ppeer]

Thanks again, I am now using your suggestion (-2)

Jarek Kujawa schreef: