Prev: Hyperlinks for .rm files stopped working
Next: How do I maintain format in programs.
From: Gary Keramidas on 22 May 2010 14:55
ron:

would something like this be simpler than using regex? i know it's powerful,
but i have a hard time understanding it.

just wondering

Sub test()
Dim lastQt As Long
Dim lastrow As Long
Dim ws As Worksheet
Dim cell As Range
Set ws = Worksheets("Sheet1")
lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row

For Each cell In ws.Range("A1:A" & lastrow)
lastQt = InStrRev(cell.Value, """")
If lastQt > 0 Then
cell.Offset(, 1).Value = Trim(Left(cell.Value, lastQt))
cell.Offset(, 2).Value = Trim(Right(cell.Value, Len(cell.Value) _
- lastQt))
End If
Next
End Sub

--


Gary Keramidas
Excel 2003


"Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message
news:3oufv5tnua01fu5u8kse1kur0m1eehtcqf(a)4ax.com...
> On Sat, 22 May 2010 04:36:01 -0700, mgbcab
> <mgbcab(a)discussions.microsoft.com>
> wrote:
>
>>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

From: Ron Rosenfeld on 22 May 2010 15:19
On Sat, 22 May 2010 14:55:33 -0400, "Gary Keramidas" <gkeramidas(a)MSN.com>
wrote:

>ron:
>
>would something like this be simpler than using regex? i know it's powerful,
>but i have a hard time understanding it.
>
>just wondering
>
>Sub test()
>Dim lastQt As Long
>Dim lastrow As Long
>Dim ws As Worksheet
>Dim cell As Range
>Set ws = Worksheets("Sheet1")
>lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
>
>For Each cell In ws.Range("A1:A" & lastrow)
> lastQt = InStrRev(cell.Value, """")
> If lastQt > 0 Then
> cell.Offset(, 1).Value = Trim(Left(cell.Value, lastQt))
> cell.Offset(, 2).Value = Trim(Right(cell.Value, Len(cell.Value) _
> - lastQt))
>End If
>Next
>End Sub

Your approach would probably run faster. But "simpler" is in the eye of the
beholder. It's pretty simple for me to devise and test a regex, so I save
"development" time. Especially if the initially given parameters are
incomplete, as is frequently the case, and require modifications.

For example, we don't know how the OP wants to handle entries that do NOT have
measurements. I chose to put the non-measurement part in the same column as
the other part descriptions; you ignore it; p45cal has it in the measurements
column.

For me to change the treatment of that instance requires only a small change in
the regex pattern.

By the way, given the OP's requirements, your lastQt line should probably be:

lastQt = WorksheetFunction.Max(InStrRev(cell.Value, """"), _
InStrRev(cell.Value, "'"))

--ron
From: Gary Keramidas on 22 May 2010 15:25
ok.

--


Gary Keramidas
Excel 2003


"Ron Rosenfeld" <ronrosenfeld(a)nospam.org> wrote in message
news:jkagv5tpe1phq0juqh2jaks4vni83tmh38(a)4ax.com...
> On Sat, 22 May 2010 14:55:33 -0400, "Gary Keramidas" <gkeramidas(a)MSN.com>
> wrote:
>
>>ron:
>>
>>would something like this be simpler than using regex? i know it's
>>powerful,
>>but i have a hard time understanding it.
>>
>>just wondering
>>
>>Sub test()
>>Dim lastQt As Long
>>Dim lastrow As Long
>>Dim ws As Worksheet
>>Dim cell As Range
>>Set ws = Worksheets("Sheet1")
>>lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
>>
>>For Each cell In ws.Range("A1:A" & lastrow)
>> lastQt = InStrRev(cell.Value, """")
>> If lastQt > 0 Then
>> cell.Offset(, 1).Value = Trim(Left(cell.Value, lastQt))
>> cell.Offset(, 2).Value = Trim(Right(cell.Value, Len(cell.Value) _
>> - lastQt))
>>End If
>>Next
>>End Sub
>
> Your approach would probably run faster. But "simpler" is in the eye of
> the
> beholder. It's pretty simple for me to devise and test a regex, so I save
> "development" time. Especially if the initially given parameters are
> incomplete, as is frequently the case, and require modifications.
>
> For example, we don't know how the OP wants to handle entries that do NOT
> have
> measurements. I chose to put the non-measurement part in the same column
> as
> the other part descriptions; you ignore it; p45cal has it in the
> measurements
> column.
>
> For me to change the treatment of that instance requires only a small
> change in
> the regex pattern.
>
> By the way, given the OP's requirements, your lastQt line should probably
> be:
>
> lastQt = WorksheetFunction.Max(InStrRev(cell.Value, """"), _
> InStrRev(cell.Value, "'"))
>
> --ron

From: Ron Rosenfeld on 22 May 2010 15:30
On Sat, 22 May 2010 11:44:36 -0400, Ron Rosenfeld <ronrosenfeld(a)nospam.org>
wrote:

>On Sat, 22 May 2010 04:36:01 -0700, mgbcab <mgbcab(a)discussions.microsoft.com>
>wrote:
>
>>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

Slight change in re.pattern to remove any leading and/or trailing spaces:

re.Pattern = "([\s\S]*?)\s*([^'""]+?)\s*$"
--ron
From: mgbcab on 23 May 2010 07:50
Thanks for the responses, unfortunately I left the spreadsheet at work and
will have to try the examples on Monday.


"bala_vb" wrote:

>
> mgbcab;955418 Wrote:
> > 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 have attached sample spread sheet with solution, please refer to it,
>
> assuming the input values in column A, try this formula in column B,
>
> =reversestring(CONCATENATE(LEFT(reversestring(A2),(IF(ISERROR(SEARCH("""",reversestring(A2),1)),SEARCH("'",reversestring(A2),1),SEARCH("""",reversestring(A2),1)))-1),",",RIGHT(reversestring(A2),LEN(reversestring(A2))-(IF(ISERROR(SEARCH("""",reversestring(A2),1)),SEARCH("'",reversestring(A2),1),SEARCH("""",reversestring(A2),1))-1))))
>
> and reversestring is a user defined vba function.
>
> 'created and edited by bala sesharao
> Option Explicit
>
> Public Function ReverseString(Text As String)
>
> ReverseString = StrReverse(Text)
>
> End Function
>
>
> all the best.
>
>
> +-------------------------------------------------------------------+
> |Filename: macro.zip |
> |Download: http://www.excelbanter.com/attachment.php?attachmentid=150|
> +-------------------------------------------------------------------+
>
>
>
> --
> bala_vb
> .
>
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4
Prev: Hyperlinks for .rm files stopped working
Next: How do I maintain format in programs.