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


On 3 Lis, 10:57, ppeer wrote:
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

On 3 nov, 11:45, Jarek Kujawa wrote:
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

On 3 Lis, 10:57, ppeer wrote:



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- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven

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


On 3 Lis, 11:53, ppeer wrote:
On 3 nov, 11:45, Jarek Kujawa wrote:





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

On 3 Lis, 10:57, ppeer wrote:

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- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven

Works Perfectly!!! Thanks a lot...- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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

Jarek Kujawa schreef:
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


On 3 Lis, 11:53, ppeer wrote:
On 3 nov, 11:45, Jarek Kujawa wrote:





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

On 3 Lis, 10:57, ppeer wrote:

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- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven

Works Perfectly!!! Thanks a lot...- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -