truncation problem

[mbk141]

Can someone help me with this. I've been away from VBA for over 2 year
and my mind is rusty.

I have a range selected that will be letters, then numbers as one word
I want to add a hypthen in between the letters and numbers. How can i d
this VBA wise?

Ex.

abc123 -> abc-123
ghj1234 -> ghj-1234
wa76 -> wa-76

Thanks alot guys,
mb

 

[Dave Peterson]

I'd do something like:

Option Explicit
Sub testme01()

Dim myRng As Range
Dim myCell As Range
Dim iCtr As Long

Set myRng = Selection

For Each myCell In myRng.Cells
With myCell
For iCtr = 1 To Len(.Value)
If IsNumeric(Mid(.Value, iCtr, 1)) Then
.Value = Left(.Value, iCtr - 1) & "-" & Mid(.Value, iCtr)
Exit For
End If
Next iCtr
End With
Next myCell
End Sub

 

[Ron Rosenfeld]

Can someone help me with this. I've been away from VBA for over 2 years
and my mind is rusty.

I have a range selected that will be letters, then numbers as one word.
I want to add a hypthen in between the letters and numbers. How can i do
this VBA wise?

Ex.

abc123 -> abc-123
ghj1234 -> ghj-1234
wa76 -> wa-76

Thanks alot guys,
mbk

There are probably better ways, but this should work:

==============
Function InsertHyphen(str As String)
Dim i As Integer

For i = 1 To Len(str)
If IsNumeric(Mid(str, i, 1)) Then Exit For
Next i

InsertHyphen = Left(str, i - 1) & "-" & Right(str, Len(str) - i + 1)

End Function
=====================


--ron