truncation problem
 

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

--
mbk14
-----------------------------------------------------------------------
mbk141's Profile: http://www.excelforum.com/member.php...fo&userid=1575
View this thread: http://www.excelforum.com/showthread.php?threadid=27267

truncation problem
 

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



mbk141 wrote:

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

--
mbk141
------------------------------------------------------------------------
mbk141's Profile: http://www.excelforum.com/member.php...o&userid=15757
View this thread: http://www.excelforum.com/showthread...hreadid=272676

--

Dave Peterson

truncation problem
 

On Tue, 26 Oct 2004 19:57:07 -0500, mbk141
wrote:


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