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.

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.

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.

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.

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.

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.