Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA for Inputting a "-"
Hello from Steve Dee
In Column B:B I have a contract Number for example 789-6 I would like the above to change to please 7-89-6 Another example I have X677-6 I would like the above to change to please X6-77-6 The script would look for the first Digit and then insert "-" Now if it found a "X" it would insert "-" after the next digit for example X1- or X7- or X5- Is it possible please to have the above Look in B:B and acheive the "-" meaning inputting 7- or X6- using the same script. Thankyou. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA for Inputting a "-"
It seems like there should be a shorter formula than this, but it does
appear to do what you asked... =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")))&"-"&MID(A1,1+MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123 456789")),9) -- Rick (MVP - Excel) "Steved" wrote in message ... Hello from Steve Dee In Column B:B I have a contract Number for example 789-6 I would like the above to change to please 7-89-6 Another example I have X677-6 I would like the above to change to please X6-77-6 The script would look for the first Digit and then insert "-" Now if it found a "X" it would insert "-" after the next digit for example X1- or X7- or X5- Is it possible please to have the above Look in B:B and acheive the "-" meaning inputting 7- or X6- using the same script. Thankyou. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA for Inputting a "-"
Actually, assuming your last five characters are *always* three digits, a
dash and a single digit, then there is a simpler formula... =LEFT(A1,LEN(A1)-4)&"-"&MID(A1,LEN(A1)-3,9) -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... It seems like there should be a shorter formula than this, but it does appear to do what you asked... =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")))&"-"&MID(A1,1+MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123 456789")),9) -- Rick (MVP - Excel) "Steved" wrote in message ... Hello from Steve Dee In Column B:B I have a contract Number for example 789-6 I would like the above to change to please 7-89-6 Another example I have X677-6 I would like the above to change to please X6-77-6 The script would look for the first Digit and then insert "-" Now if it found a "X" it would insert "-" after the next digit for example X1- or X7- or X5- Is it possible please to have the above Look in B:B and acheive the "-" meaning inputting 7- or X6- using the same script. Thankyou. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA for Inputting a "-"
Sorry, I just noticed you wanted a VBA macro...
Sub InsertDash() Dim R As Range Dim S As String For Each R In Intersect(ActiveSheet.UsedRange, Columns("B")) S = R.Value If Not S Like "*-*-*" Then If IsNumeric(Left(S, 1)) Then R.Value = Left(S, 1) & "-" & Mid(S, 2) Else R.Value = Left(S, 2) & "-" & Mid(S, 3) End If End If Next End Sub -- Rick (MVP - Excel) "Steved" wrote in message ... Hello from Steve Dee In Column B:B I have a contract Number for example 789-6 I would like the above to change to please 7-89-6 Another example I have X677-6 I would like the above to change to please X6-77-6 The script would look for the first Digit and then insert "-" Now if it found a "X" it would insert "-" after the next digit for example X1- or X7- or X5- Is it possible please to have the above Look in B:B and acheive the "-" meaning inputting 7- or X6- using the same script. Thankyou. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA for Inputting a "-"
Hello Rick
Firstly I do not know how to put scripts off this nature together Is it possible please to have below run it, I tried my self but could not get it to work. I thankyou for your time on my issue. Sub InputDash() 'Do Until ActiveCell = "" For x = 1 To 1 Dim Rng As Range Set Rng = Range("B2", Range("B56000").End(xlUp)) Range("B2").Select For Each cell In Rng If cell.Value < "" Then ActiveCell.Offset(0, 1).Select ActiveCell.FormulaR1C1 =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")))&"-"&MID(A1,1+MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123 456789")),9) ActiveCell.Offset(1, -1).Select End If Next cell Next x 'Loop End Sub "Rick Rothstein" wrote: It seems like there should be a shorter formula than this, but it does appear to do what you asked... =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"012345 6789")))&"-"&MID(A1,1+MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123 456789")),9) -- Rick (MVP - Excel) "Steved" wrote in message ... Hello from Steve Dee In Column B:B I have a contract Number for example 789-6 I would like the above to change to please 7-89-6 Another example I have X677-6 I would like the above to change to please X6-77-6 The script would look for the first Digit and then insert "-" Now if it found a "X" it would insert "-" after the next digit for example X1- or X7- or X5- Is it possible please to have the above Look in B:B and acheive the "-" meaning inputting 7- or X6- using the same script. Thankyou. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA for Inputting a "-"
Hello Rick
Excellent Value as always Thankyou Steved "Rick Rothstein" wrote: Sorry, I just noticed you wanted a VBA macro... Sub InsertDash() Dim R As Range Dim S As String For Each R In Intersect(ActiveSheet.UsedRange, Columns("B")) S = R.Value If Not S Like "*-*-*" Then If IsNumeric(Left(S, 1)) Then R.Value = Left(S, 1) & "-" & Mid(S, 2) Else R.Value = Left(S, 2) & "-" & Mid(S, 3) End If End If Next End Sub -- Rick (MVP - Excel) "Steved" wrote in message ... Hello from Steve Dee In Column B:B I have a contract Number for example 789-6 I would like the above to change to please 7-89-6 Another example I have X677-6 I would like the above to change to please X6-77-6 The script would look for the first Digit and then insert "-" Now if it found a "X" it would insert "-" after the next digit for example X1- or X7- or X5- Is it possible please to have the above Look in B:B and acheive the "-" meaning inputting 7- or X6- using the same script. Thankyou. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
inputting data with a "+" sign | Excel Discussion (Misc queries) |