Add comma after last " in a cell

  • Thread starter Thread starter mgbcab
  • Start date Start date
M

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.
 
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
 
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
Click to expand...

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
 
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.
Click to expand...

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
 
Which is why I included a disclaimer:
Click to expand...

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
 
mgbcab;955418 said:
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.
Click to expand...

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
+-------------------------------------------------------------------
 
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 said:
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.
Click to expand...

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
Click to expand...
 
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
Click to expand...

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
 
ok.

--


Gary Keramidas
Excel 2003


Ron Rosenfeld said:
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
Click to expand...
 
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.
Click to expand...

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
Click to expand...

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

re.Pattern = "([\s\S]*?)\s*([^'""]+?)\s*$"
--ron
 
Thanks for the responses, unfortunately I left the spreadsheet at work and
will have to try the examples on Monday.
 
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.
 
Back
Top