Hi Claus,
You have the right idea, but when I execute it for some reason it fixes all the cells in the file
Hi Ron,
This is my first time using UDF and did not work for me. Don't know what I missed. I copied and pasted the data provided into a module, then I added =ReArrangeText(D8)into a cell, then I called it in a function and it asked me to select a cell or range so when I selected cell D8 nothing happened and when I selected a range from D5 to D12 nothing either
I'm providing additional information and hope it will help me explain my issue better. The data that needs fixing is on Column D only and starts at Row 5 and goes down from there.
Original sample data from D5 thru D12
(D5)Ø.938 +.010/-.001 - 7 places
(D6)¨.39 +/-.03
(D7)|?|?0.030|A|B|
(D8)Diameter - Basic: = 2.800
(D9)Ø.266 +.006/-.001
(D10)Angle Dimension - Basic: = 4.72°
(D11)Angle Dimension - Basic: = 5.2° - 3 Places
(D12)Linear Dimension - Basic: = .660 - 3 Places
==================================================
Desired sample data from D5 thru D12
(D5)Ø.938 +.010/-.001 - 7 places
(D6)¨.39 +/-.03
(D7)|?|?0.030|A|B|
(D8)Ø2.800 BASIC
(D9)Ø.266 +.006/-.001
(D10)4.72° BASIC
(D11)5.2° BASIC - 3 Places
(D12).660 BASIC - 3 Places
Thanks for your help
After thinking about it for a bit, here is a routine, based on using regular expressions to examine the different parts of the data, that works on the examples you have provided thus far. It is a macro, rather than a function, so it can change the original cells. However, as written, for debugging purposes it places the results in the adjacent column.
By examining the macro and the comment, you should see near the beginning how you can change rDest to refer to the starting cell of wherever you want to put the results (including the original "D5", once debugged) or in some other cell if you do not want to change what is in column E.
The macro assumes your data starts in D5, and the only data below that in column D is data to be acted upon. It uses similar logic as does the UDF, but has been modified to account for the extra examples you have provided.
To enter this Macro (Sub), <alt-F11> opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.
To use this Macro (Sub), <alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
======================================
Option Explicit
Option Compare Text
Sub ReArrangeText()
Dim re As Object, mc As Object
Dim s As String, sIn As String, sPl As String
Dim sVal As String
Dim rSrc As Range, rDest As Range
Dim v As Variant
Dim i As Long
Set rSrc = Range("D5", Cells(Rows.Count, "D").End(xlUp))
Set rDest = Range("E5") 'when working properly, change this to D5
v = rSrc
Set re = CreateObject("vbscript.regexp")
With re
.Global = True
.ignorecase = True
.Pattern = "^.*basic.*?(\xD8?\d*\.?\d+\b\xB0?)\s*(deg|in)?\D*(\d+\s+places)?"
End With
For i = LBound(v) To UBound(v)
s = v(i, 1)
If re.test(s) = True Then
Set mc = re.Execute(s)
sVal = mc(0).submatches(0) 'Value
sIn = mc(0).submatches(1) 'Inches abbrev
sPl = mc(0).submatches(2) 'Places
v(i, 1) = IIf(sPl = "" And Right(sVal, 1) <> Chr(176) And _
(sIn = "" Or sIn = "in"), Chr(216), "") & _
sVal & IIf(sIn = "deg", Chr(176), "") & _
" BASIC " & IIf(sPl <> "", " - ", "") & UCase(sPl)
End If
Next i
rDest.Resize(rowsize:=UBound(v)) = v
End Sub
==================================
