Need help adding a front slash "/" in between numbers that do nothave it. Examples included.

ezduzitez · May 28, 2013

To all,

The number vary from file to file and from row to row, but one thing they do have in common is the front slash "/" separating the "+" highest tolerance from the "-" lowest tolerance.

IS S/B

Ø.938 +.010 -.001 | Ø.938 +.010/-.001
Ø.266 +.006 -.001 | Ø.266 +.006/-.001
1.820 +/-.003 | This one is OK
Ø1.942 +/-.005 | This one is OK
..539 +.005 -.000 | .539 +.005/-.000
..271 +.002 -.000 | .271 +.002/-.000

Not all of them require fixing, but many of them do as noted above. The numbers vary all the time so I've had to go in and add the missing slash "/" manually where needed.

Once again your help is greatly appreciated.

Sincerely,

EZ

Claus Busch · May 28, 2013

Hi,

Am Tue, 28 May 2013 13:21:33 -0700 (PDT) schrieb (e-mail address removed):

IS S/B

Ø.938 +.010 -.001 | Ø.938 +.010/-.001
Ø.266 +.006 -.001 | Ø.266 +.006/-.001
1.820 +/-.003 | This one is OK
Ø1.942 +/-.005 | This one is OK
.539 +.005 -.000 | .539 +.005/-.000
.271 +.002 -.000 | .271 +.002/-.000

in B2 try:
=IF(ISNUMBER(FIND("/",A2)),A2,SUBSTITUTE(A2," -","/-"))

Regards
Claus Busch

ezduzitez · May 28, 2013

Thanks Claus,

I can put things together, but I cannot get it started. Would you mind describing the macro as a single module?

Sub slash()

' needed data

End sub

Would like to have a macro place a front slash "/" between a set of numbersthat are different all the time disregarding rows that do not need change.

One more thing I failed to mention is that the data is in column D only andgoes from D5 down.

The numbers vary from file to file and from row to row, but one thing they do have in common is the front slash "/" separating the "+" highest tolerance from the "-" lowest tolerance.

IS S/B

Ø.938 +.010 -.001 | Ø.938 +.010/-.001
Ø.266 +.006 -.001 | Ø.266 +.006/-.001
1.820 +/-.003 | This one is OK
Ø1.942 +/-.005 | This one is OK
..539 +.005 -.000 | .539 +.005/-.000
..271 +.002 -.000 | .271 +.002/-.000
21 +0 -2° | 21 +0/-2°
97° BASIC | This one is OK

Not all of them require fixing, but many of them do as noted above. The numbers vary all the time so I've had to go in and add the missing slash "/" manually where needed.

Once again your help is greatly appreciated.

Sincerely,

EZ

Claus Busch · May 28, 2013

Hi,

Am Tue, 28 May 2013 14:02:51 -0700 (PDT) schrieb (e-mail address removed):

Would like to have a macro place a front slash "/" between a set of numbers that are different all the time disregarding rows that do not need change.

try (for column A):

Sub Slash()
Dim LRow As Long
Dim rngC As Range

'Modify to suit
LRow = Cells(Rows.Count, 1).End(xlUp).Row
For Each rngC In Range("A1:A" & LRow)
If InStr(rngC, "/") = 0 Then
rngC = Replace(rngC, " -", "/-")
End If
Next
End Sub

Regards
Claus Busch

Claus Busch · May 28, 2013

Hi again,

I didn't read correctly,

Try:
Sub Slash()
Dim LRow As Long
Dim rngC As Range

'Modify to suit
LRow = Cells(Rows.Count, 4).End(xlUp).Row
For Each rngC In Range("D5

" & LRow)
If InStr(rngC, "/") = 0 Then
rngC = Replace(rngC, " -", "/-")
End If
Next
End Sub

Regards
Claus Busch

Ron Rosenfeld · May 29, 2013

Thanks Claus,

I can put things together, but I cannot get it started. Would you mind describing the macro as a single module?

Sub slash()

' needed data

End sub

Would like to have a macro place a front slash "/" between a set of numbers that are different all the time disregarding rows that do not need change.

One more thing I failed to mention is that the data is in column D only and goes from D5 down.

The numbers vary from file to file and from row to row, but one thing they do have in common is the front slash "/" separating the "+" highest tolerance from the "-" lowest tolerance.

IS S/B

Ø.938 +.010 -.001 | Ø.938 +.010/-.001
Ø.266 +.006 -.001 | Ø.266 +.006/-.001
1.820 +/-.003 | This one is OK
Ø1.942 +/-.005 | This one is OK
.539 +.005 -.000 | .539 +.005/-.000
.271 +.002 -.000 | .271 +.002/-.000
21 +0 -2° | 21 +0/-2°
97° BASIC | This one is OK

Not all of them require fixing, but many of them do as noted above. The numbers vary all the time so I've had to go in and add the missing slash "/" manually where needed.

Once again your help is greatly appreciated.

Sincerely,

EZ

If your real data has the same format as your example, then this macro will do the job also:

==================================
Option Explicit
Sub InsertSlash()
Dim re As Object
Dim v As Variant
Dim i As Long
Dim r As Range

Set r = Range("D5", Cells(Rows.Count, "D").End(xlUp))
v = r

Set re = CreateObject("vbscript.regexp")
re.Pattern = "[^/]-\s*([\d.]+)"

For i = LBound(v) To UBound(v)
v(i, 1) = re.Replace(v(i, 1), "/-$1")
Next i
r = v
End Sub
===========================

Ron Rosenfeld · May 29, 2013

Minor change in re.pattern and in the replacement string to account for some possible variations in your data.

==================================
Option Explicit
Sub InsertSlash()
Dim re As Object
Dim v As Variant
Dim i As Long
Dim r As Range

Set r = Range("D5", Cells(Rows.Count, "D").End(xlUp))
v = r

Set re = CreateObject("vbscript.regexp")
re.Pattern = "([^/])\s*-\s*([\d.]+)"

For i = LBound(v) To UBound(v)
v(i, 1) = re.Replace(v(i, 1), "$1/-$2")
Next i
r = v
End Sub
===========================

ezduzitez · May 29, 2013

Thanks again Claus and Ron,

They both work great for the examples I provided. After running the macros I realized I missed a few more details as noted below. It's exactly what I need when there's no "PLACES" called out.

IS S/B
Ø.938 +.010 -.001 - 7 PLACES | Ø.938 +.010/-.001 - 7 PLACES
After running macro there's an extra slash Ø.938 +.010/-.001/- 7 PLACES

21 +0 -2° - 2 PLACES | 21 +0/-2° - 2 PLACES
After running macro there's an extra slash 21 +0/-2°/- 2 PLACES

The number of places vary all the time also. Thanks again for all your help..

EZ

Claus Busch · May 29, 2013

Hi,

Am Wed, 29 May 2013 09:45:55 -0700 (PDT) schrieb (e-mail address removed):

IS S/B
Ø.938 +.010 -.001 - 7 PLACES | Ø.938 +.010/-.001 - 7 PLACES
After running macro there's an extra slash Ø.938 +.010/-.001/- 7 PLACES

21 +0 -2° - 2 PLACES | 21 +0/-2° - 2 PLACES
After running macro there's an extra slash 21 +0/-2°/- 2 PLACES

try:
Sub Slash()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, 4).End(xlUp).Row
For Each rngC In Range("D5

" & LRow)
If InStr(rngC, "/") = 0 Then
rngC = Replace(rngC, " -.", "/-.")
End If
Next
End Sub

Regards
Claus Busch

Claus Busch · May 29, 2013

Hi,

Am Wed, 29 May 2013 18:59:11 +0200 schrieb Claus Busch:

Sub Slash()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, 4).End(xlUp).Row
For Each rngC In Range("D5" & LRow)
If InStr(rngC, "/") = 0 Then
rngC = Replace(rngC, " -.", "/-.")
End If
Next
End Sub

or try:
Sub Slash1()
Dim LRow As Long

LRow = Cells(Rows.Count, 4).End(xlUp).Row
Range("D5

" & LRow).Replace What:=" -.", _
Replacement:="/-.", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
End Sub

Regards
Claus Busch

ezduzitez · May 29, 2013

That took care of it. Thanks again for your help and support.

Sincerely,

EZ

ezduzitez · May 29, 2013

Oops. Just noticed one last minor detail. It erases the slash from the whole number cells

IS 21 +0 -2° - 2 PLACES | S/B 21 +0/-2° - 2 PLACES
After running latest macro is 21 +0 -2° - 2 PLACES

Thanks again.

Claus Busch · May 29, 2013

Hi,

Am Wed, 29 May 2013 10:26:32 -0700 (PDT) schrieb (e-mail address removed):

IS 21 +0 -2° - 2 PLACES | S/B 21 +0/-2° - 2 PLACES
After running latest macro is 21 +0 -2° - 2 PLACES

try:
Sub Slash()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, 4).End(xlUp).Row
For Each rngC In Range("D5

" & LRow)
rngC = Replace(rngC, " -", "/-", , 1)
Next
End Sub

Regards
Claus Busch

ezduzitez · May 29, 2013

Appreciate the troubleshooting Claus.

It does everything right now, except it goes back to placing the "/" beforethe "- PLACES" on very few random callouts with "- PLACES"

Here's a few examples after running latest macro.

51.4° BASIC /- 6 PLACES
5.2° BASIC /- 3 PLACES
..660 BASIC /- 3 PLACES
1.076 +.000/-.006/- 3 PLACES

Thanks again,

EZ

Claus Busch · May 29, 2013

Hi,

Am Wed, 29 May 2013 12:23:59 -0700 (PDT) schrieb (e-mail address removed):

51.4° BASIC /- 6 PLACES
5.2° BASIC /- 3 PLACES
.660 BASIC /- 3 PLACES
1.076 +.000/-.006/- 3 PLACES

I hope that this code will work for all cases:

Sub Slash()
Dim LRow As Long
Dim rngC As Range

LRow = Cells(Rows.Count, 4).End(xlUp).Row
For Each rngC In Range("D5

" & LRow)
If InStr(rngC, "+") > 0 And InStr(rngC, "/") = 0 Then
rngC = Replace(rngC, " -", "/-", , 1)
End If
Next
End Sub

Regards
Claus Busch

Ron Rosenfeld · May 29, 2013

Thanks again Claus and Ron,

They both work great for the examples I provided. After running the macros I realized I missed a few more details as noted below. It's exactly what I need when there's no "PLACES" called out.

IS S/B
Ø.938 +.010 -.001 - 7 PLACES | Ø.938 +.010/-.001 - 7 PLACES
After running macro there's an extra slash Ø.938 +.010/-.001/- 7 PLACES

21 +0 -2° - 2 PLACES | 21 +0/-2° - 2 PLACES
After running macro there's an extra slash 21 +0/-2°/- 2 PLACES

The number of places vary all the time also. Thanks again for all your help.

EZ

Very small change to take care of that problem:
(setting the re.global parameter to false ensures that only the first "-\" will be processed.

===================================
Option Explicit
Sub InsertSlash()
Dim re As Object
Dim v As Variant
Dim i As Long
Dim r As Range

Set r = Range("D5", Cells(Rows.Count, "D").End(xlUp))
v = r

Set re = CreateObject("vbscript.regexp")
With re
.Pattern = "([^/])\s*-\s*([\d.]+)"
.Global = False

For i = LBound(v) To UBound(v)
v(i, 1) = .Replace(v(i, 1), "$1/-$2")
Next i
End With
r = v
End Sub
==================================

Need help adding a front slash "/" in between numbers that do nothave it. Examples included.

ezduzitez

Claus Busch

ezduzitez

Claus Busch

Claus Busch

Ron Rosenfeld

Ron Rosenfeld

ezduzitez

Claus Busch

Claus Busch

ezduzitez

ezduzitez

Claus Busch

ezduzitez

Claus Busch

Ron Rosenfeld