Add comma after last " in a cell
 

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.

Add comma after last " in a cell
 

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

--
Biff
Microsoft Excel MVP


"mgbcab" wrote in message
...
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.

Add comma after last " in a cell
 

On Sat, 22 May 2010 10:30:42 -0400, "T. Valko" wrote:

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

Add comma after last " in a cell
 

Which is why I included a disclaimer:

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

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Sat, 22 May 2010 10:30:42 -0400, "T. Valko"
wrote:

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

Add comma after last " in a cell
 

On Sat, 22 May 2010 12:12:59 -0400, "T. Valko" wrote:

Which is why I included a disclaimer:

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

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

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)),SEAR CH("'",reversestring(A2),1),SEARCH("""",reversestr ing(A2),1)))-1),",",RIGHT(reversestring(A2),LEN(reversestring(A 2))-(IF(ISERROR(SEARCH("""",reversestring(A2),1)),SEAR CH("'",reversestring(A2),1),SEARCH("""",reversestr ing(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.

Add comma after last " in a cell
 

On Sat, 22 May 2010 14:55:33 -0400, "Gary Keramidas"
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

Add comma after last " in a cell
 

ok.

--


Gary Keramidas
Excel 2003


"Ron Rosenfeld" wrote in message
...
On Sat, 22 May 2010 14:55:33 -0400, "Gary Keramidas"
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