Ultimately, I want to do a text to columns method but 1st I need to make the
data work properly.
I have close to 9000 items of vary degrees of numbers and text. I want a
formula that will insert a comma after the last " or ' in a cell, reading
from left.
3/4" x 1/2" pvc pipe
1/2" x 1/2" x 6' black pipe
1' insulated filter
This way when i do the text to columns, i can separate the by commas. The
purpose is to separate the sizes and the descriptions.
I would suggest a macro that does the parsing for you.
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), first select the range you wish to parse. (There are
other methods to set up this range more automatically, but that needs a better
description of your data).
<alt-F8> opens the macro dialog box. Select the macro by name, and <RUN>.
As written, the macro will put the two segments in the adjacent columns, but
you can change the OFFSET's to enable it to erase the original data, if that is
more desirable.
====================================
Option Explicit
Sub ParseLengths()
Dim rg As Range, c As Range
Dim re As Object, mc As Object
Dim s As String
Set rg = Selection
Set re = CreateObject("vbscript.regexp")
re.Pattern = "([\s\S]*?)([^'""]*$)"
For Each c In rg
Range(c.Offset(0, 1), c.Offset(0, 2)).ClearContents
s = c.Value
If re.test(s) = True Then
Set mc = re.Execute(s)
c.Offset(0, 1).Value = mc(0).submatches(0)
c.Offset(0, 2).Value = mc(0).submatches(1)
End If
Next c
End Sub
===================================
--ron