Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
truncation of y-axis text | Charts and Charting in Excel | |||
Print truncation | Excel Discussion (Misc queries) | |||
Copy range content as picture - truncation problem | Excel Programming | |||
Copy range content as picture - truncation problem | Excel Programming | |||
string truncation | Excel Programming |