split cell in number-cell and letter-cell
 

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

split cell in number-cell and letter-cell
 

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

split cell in number-cell and letter-cell
 

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...

split cell in number-cell and letter-cell
 

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 -

Yes
 

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 -