Add comma after last " in a cell

[mgbcab]

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.

 

[p45cal]

Put these two macros in a standard code module, select the cells yo
want to process then
run blah which will place the results in the cell to the right so tha
you can check it works ok, and/or:
run blah2 which will replace the values in the selected cells.


VBA Code:
--------------------


Sub blah() 'puts results in cell to right.
For Each cll In Selection.Cells
If InStr(cll.Value, """") + InStr(cll.Value, "'") > 0 Then
xx = InStrRev(cll.Value, """")
yy = InStrRev(cll.Value, "'")
zz = Application.Max(xx, yy)
cll.Offset(, 1).Value = Left(cll.Value, zz) & "," & Mid(cll.Value, zz + 1)
Else
cll.Offset(, 1).Value = cll.Value
End If
Next cll
End Sub

Sub blah2() 'replaces cell value in situ.
For Each cll In Selection.Cells
If InStr(cll.Value, """") + InStr(cll.Value, "'") > 0 Then
xx = InStrRev(cll.Value, """")
yy = InStrRev(cll.Value, "'")
zz = Application.Max(xx, yy)
cll.Value = Left(cll.Value, zz) & "," & Mid(cll.Value, zz + 1)
End If
Next cll
End Sub

--------------------







Ultimately, I want to do a text to columns method but 1st I need t
make the

 

[T. Valko]

For a "one off" formula solution(s)...

This formula inserts a comma at the desired location on the sample data you
posted.

A2 = 3/4" x 1/2" pvc pipe

=REPLACE(A2,LOOKUP(100,-MID(A2,ROW($1:$50),1),ROW($1:$50))+2,1,",")

Result of formula: 3/4" x 1/2",pvc pipe

These formulas will parse the string into 2 substrings.

A2 = 3/4" x 1/2" pvc pipe

Entered in B2:

=LEFT(A2,LOOKUP(100,-MID(A2,ROW($1:$50),1),ROW($1:$50))+1)

Result of formula: 3/4" x 1/2"

Entered in C2:

=MID(A2,LEN(B2)+2,50)

Result of formula: pvc pipe

 

[p45cal]

*Very* ingenious first formula Biff,:clap: though here's hoping (a
there's no numerals in the description and (b) there's always a spac
before the description, although (b) can be rectified by changng th
last '1' in your formula to a '0'. (It did take me a while to pick up o
how it worked!)

 

[T. Valko]

though here's hoping...

Which is why I included a disclaimer:

 

[Ron Rosenfeld]

For a "one off" formula solution(s)...

This formula inserts a comma at the desired location on the sample data you
posted.

A2 = 3/4" x 1/2" pvc pipe

=REPLACE(A2,LOOKUP(100,-MID(A2,ROW($1:$50),1),ROW($1:$50))+2,1,",")

Result of formula: 3/4" x 1/2",pvc pipe

These formulas will parse the string into 2 substrings.

A2 = 3/4" x 1/2" pvc pipe

Entered in B2:

=LEFT(A2,LOOKUP(100,-MID(A2,ROW($1:$50),1),ROW($1:$50))+1)

Result of formula: 3/4" x 1/2"

Entered in C2:

=MID(A2,LEN(B2)+2,50)

Result of formula: pvc pipe

Note that your comma insertion formula fails if there is a number in the data
after the final ' or "

e.g: 1/2" x 1/2" x 6' black No. 7 pipe



--ron

 

[Ron Rosenfeld]

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

 

[T. Valko]

Which is why I included a disclaimer:

This formula inserts a comma at the desired
location on the sample data you posted.

 

[Ron Rosenfeld]

Which is why I included a disclaimer:

I saw the disclaimer. But I thought it would be beneficial to the OP to
understand that you were testing for the last digit in the string, and not for
the last " or ', and what the consequences might be.
--ron

 

[bala_vb]

Ultimately, I want to do a text to columns method but 1st I need to mak
the
data work properly.

I have close to 9000 items of vary degrees of numbers and text. I wan
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
+-------------------------------------------------------------------

 

[Gary Keramidas]

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

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

 

[Ron Rosenfeld]

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

 

[Gary Keramidas]

ok.

--


Gary Keramidas
Excel 2003

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

 

[Ron Rosenfeld]

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

 

[mgbcab]

Thanks for the responses, unfortunately I left the spreadsheet at work and
will have to try the examples on Monday.

 

[mgbcab]

I used MVP's, Ron's and T.Valko (left&mid) examples. All worked very well
with minor issues related to data in my fields. But correcting less than 50
cells is better than 9000 cells. Thanks again for your help.

 

[T. Valko]

You're welcome. Thanks for the feedback!