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.
0
Utf
5/22/2010 11:36:01 AM
excel.misc 78881 articles. 5 followers. Follow

16 Replies
2586 Views

Similar Articles

[PageSpeed] 17

Put these two macros in a standard code module, select the cells you
want to process then
run blah which will place the results in the cell to the right so that
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
  
--------------------








mgbcab;729551 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.


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204471

http://www.thecodecage.com/forumz

0
p45cal
5/22/2010 1:41:52 PM
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" <mgbcab@discussions.microsoft.com> wrote in message 
news:33346750-6F48-419E-8CC0-4E263A2BD8E4@microsoft.com...
> 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. 


0
T
5/22/2010 2:30:42 PM
*Very* ingenious first formula Biff,:clap: though here's hoping (a)
there's no numerals in the description and (b) there's always a space
before the description, although (b) can be rectified by changng the
last '1' in your formula to a '0'. (It did take me a while to pick up on
how it worked!)


-- 
p45cal

*p45cal*
------------------------------------------------------------------------
p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=204471

http://www.thecodecage.com/forumz

0
p45cal
5/22/2010 3:14:39 PM
>though here's hoping...

Which is why I included a disclaimer:

>...the sample data you posted.

-- 
Biff
Microsoft Excel MVP


"p45cal" <p45cal.4bdctx@thecodecage.com> wrote in message 
news:p45cal.4bdctx@thecodecage.com...
>
> *Very* ingenious first formula Biff,:clap: though here's hoping (a)
> there's no numerals in the description and (b) there's always a space
> before the description, although (b) can be rectified by changng the
> last '1' in your formula to a '0'. (It did take me a while to pick up on
> how it worked!)
>
>
> -- 
> p45cal
>
> *p45cal*
> ------------------------------------------------------------------------
> p45cal's Profile: http://www.thecodecage.com/forumz/member.php?u=558
> View this thread: 
> http://www.thecodecage.com/forumz/showthread.php?t=204471
>
> http://www.thecodecage.com/forumz
> 


0
T
5/22/2010 3:24:32 PM
On Sat, 22 May 2010 10:30:42 -0400, "T. Valko" <biffinpitt@comcast.net> 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
0
Ron
5/22/2010 3:40:45 PM
On Sat, 22 May 2010 04:36:01 -0700, mgbcab <mgbcab@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
0
Ron
5/22/2010 3:44:36 PM
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" <ronrosenfeld@nospam.org> wrote in message 
news:s1ufv599ddlc6n3799vtv8hea0pm4476lr@4ax.com...
> On Sat, 22 May 2010 10:30:42 -0400, "T. Valko" <biffinpitt@comcast.net> 
> 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 


0
T
5/22/2010 4:12:59 PM
On Sat, 22 May 2010 12:12:59 -0400, "T. Valko" <biffinpitt@comcast.net> 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
0
Ron
5/22/2010 4:42:53 PM
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
0
bala_vb
5/22/2010 5:56:47 PM
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@nospam.org> wrote in message 
news:3oufv5tnua01fu5u8kse1kur0m1eehtcqf@4ax.com...
> On Sat, 22 May 2010 04:36:01 -0700, mgbcab 
> <mgbcab@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 

0
Gary
5/22/2010 6:55:33 PM
On Sat, 22 May 2010 14:55:33 -0400, "Gary Keramidas" <gkeramidas@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
0
Ron
5/22/2010 7:19:29 PM
ok.

-- 


Gary Keramidas
Excel 2003


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message 
news:jkagv5tpe1phq0juqh2jaks4vni83tmh38@4ax.com...
> On Sat, 22 May 2010 14:55:33 -0400, "Gary Keramidas" <gkeramidas@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 

0
Gary
5/22/2010 7:25:54 PM
On Sat, 22 May 2010 11:44:36 -0400, Ron Rosenfeld <ronrosenfeld@nospam.org>
wrote:

>On Sat, 22 May 2010 04:36:01 -0700, mgbcab <mgbcab@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
0
Ron
5/22/2010 7:30:46 PM
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
> .
> 
0
Utf
5/23/2010 11:50:01 AM
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. 

"mgbcab" wrote:

> 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
> > .
> > 
0
Utf
5/24/2010 12:03:01 PM
You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"mgbcab" <mgbcab@discussions.microsoft.com> wrote in message 
news:419F93A0-E2A5-4FE7-8B4A-98BEEFB62C2A@microsoft.com...
>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.
>
> "mgbcab" wrote:
>
>> 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
>> > .
>> > 


0
T
5/24/2010 1:40:24 PM
Reply:

Similar Artilces:

The Sum from 1 worksheet cell to another worksheet cell
the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula qwerty: To sum the value on Sheet1, cell A10 with the cell value Sheet2, cell B20, enter =Sheet1!A10 + Sheet2!B20 (or you can enter '=' sign and click on A10, then enter the plus sign and click on B20) jeff >-----Original Message----- >the sum from one cell on sheet1 from another cell on sheet2,how do you do the formula >. > ...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Need Formula To Find Blank and NonBlank Cells
I have a worksheet with 6 columns (by Month) Sep Aug Jul Jun May Apr I have to review starting for example with May, I need to find any cell in May range that is null <> where Jun and Apr both are not null <> So if May is null and Jun and Apr are not null than I would count that as 1. If May is null and either Jun or Apr are null then I would not count them. =SUMPRODUCT(N(E2:E100=""),N(D2:D100<>""),N(F2:F100<>"")) "hilltop55" <hilltop55@discussions.microsoft.com> wrote in message news:08D989CB-D1B4-49F...

Need Syntax for "AND" to Evaluate 2 Cells
I need to evaluate 2 cells while inside an "Private Sub Worksheet_SelectionChange(ByVal Target As Range)". I thought AND would work but I cannot get it to work; I receive a syntax error on the AND(Range... line. Can someone please provide me the proper syntax to evaluate the 2 cells? Here's my code... Private Sub Worksheet_SelectionChange(ByVal Target As Range) If ActiveSheet.Name = "Sheet1" Then And(Range("I3") <> "", Range("K4") = "") Then Range("K4") = Range("K3") End...

Enter "1", cell show ".01". Why?
Any number typed into a cell is divided by 100. If proceded by "=" the number is correct. What caused this and how can I fix it? Try this .. Click Tools > Options > Edit tab Uncheck "Fixed decimal" > OK Things should be back to normal now .. (it's a fixed decimal setting !) -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Yonian" <Yonian@discussions.microsoft.com> wrote in message news:40499CA4-7FAF-42A6-8B19-A90881735C50@microsoft.com... > Any number typed into a cell is divided by 100. > If p...

selecting a cell
I seem unable to select a single cell, or a single row--click on one in the normal manner, and the two below also highlight, then delete or whatever command is given. If I input a number/text, that just goes into the one cell. tapping F8 increases this to two wide and three high automatically selected. Also, very slow to do almost anything. Thanks Pat, Are you by any chance using Excel 2007? If so there is a known bug that causes multiple cell selection and I understand this has been reported to Microsoft. If you take the zoom level up and down this is reported to cl...

How can I clear the last Data->Text to columns to formatting
I've noticed in Excel 2000 that if I paste text into various worksheets within a workbook each paste will assume the Text->Column formatting that I applied in the previous. How can I prevent it from happening ? Thanks Steve Just run another data|Text to columns against a dummy cell. Specify delimited, but remove all the check marks from all the possible delimiters. (alternatively, you can close excel and reopen it.) svaardt wrote: > > I've noticed in Excel 2000 that if I paste text into various worksheets > within a workbook each paste will assume the Text->Col...

Solver add-in and macros
I just got a new computer with XP and Excel 2003 and tried to run a macro that used to SOLVER add-in function and got a compile error. As a test I recorded a macro using the SOLVER and got the same result when I tried to run that macro. The Compile Error is "Sub or Function not defined". On the Knowledge Base it says I need to add SOLVER to the list of References in the VB Editor. When I go to the references list, SOLVER does not appear. When I open Help for VB and search for SOLVER, it finds nothing. What is going on? Why doesn't VB even recognize the existence ...

How do I add a signature
We have our purchase orders created in excel and we would like to add a "handwritten" signature" to them. How to I do this? Pen If using xl2002 or 2003...... Create a graphic with the signature then add it to the Footer when printing. Gord Dibben Excel MVP On Mon, 14 Feb 2005 09:03:08 -0800, "Penfold" <Penfold@discussions.microsoft.com> wrote: >We have our purchase orders created in excel and we would like to add a >"handwritten" signature" to them. How to I do this? ...

How to add new entity?
Hi. I'm now researching the customization abilities of MS CRM 1.2. So I have a small task, which I'm not able to accomplish with SDK. The main question is how to add additional entity to CRM? I need to add entity "Pets", and dictionary "PetTypes". Pets should be linked with Contacts (1:M). Pets should have 3 attributes: - Name (string of 150 chars), - Type (value from PetTypes dictionary), - Description (string of 1000 chars) PetTypes should have 3 entries: "Cat", "Dog" and "Parrot" I expect that i...

cell will not center
Hi. I have a user with an Excel worksheet. There are multiple rows and columns and they are all set on center alignment, (center alignment icon on the toolbar as well as Format Cells --> Horizontal Alignment --> Center.) The alphabetical characters align correctly but the numerical don't, as they will only left align. Format Cells --> Number is set to General, so I don't know why it won't change the alignment. Other than the worksheet being corrupted, I don't know what could be wrong with it. Any suggestions are much appreciated. Thanks! Hilary =?Utf-8?B?SG...

Count # of cells b/w cells ...
Hello, I have the following data in a column: 7 0 0 0 7 0 0 0 0 0 7 0 0 7 0 0 0 0 0 0 7 etc. The number of zero's between the 7's is random. I want a formula tha would count the number of zeros between the 7's. Thanks, Ari Bar -- AriBar ----------------------------------------------------------------------- AriBari's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2504 View this thread: http://www.excelforum.com/showthread.php?threadid=38806 Assume A5:A20 is the data, try this: B5 = A5+B4 (copy formula down) Now make a table with 2 column...

Too many different cell formats #6
I am running into the error message: Too many different cell formats Is there a solution to lowering the number of formats I am using? Just trying to change them to make some consistent gives me the same error message. I tried running the search on the forums on my topic but they have been disabled for a Microsoft upgrade. Thanks! One idea - Rob Bovey's excellent Utilities add-in will list all the formats in use in your workbook, allowing you to manually delete what isn't being used. http://www.appspro.com/Utilities/ExcelUtilities.htm You can also see the source code for ...

How do add another code to a current one?
I have this following code to make the rows changed based on the critea in column 16, and I need add A "Red, Yellow, Green" for status to only one column 30 at the end of the spreadsheet. How do I add another code? I keep getting an error.. Private Sub Worksheet_Change(ByVal Target As Range) Dim c As Range, clr As Long For Each c In Target.Cells If c.Column = 16 Then Select Case c.Value Case "Analyze": clr = RGB(204, 255, 255) Case "Build ": clr = RGB(204, 255, 255) Case ...

Why is Excel changing the last 2 digits of a 17 digit num to 00.
When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to 00 when I leave the cell. Format - Cell does not have a setting to stop this 'feature'. How do I make Excel recongize the large number? On Thu, 28 Jul 2005 19:09:01 -0700, "Allie" <Allie@discussions.microsoft.com> wrote: >When I enter a 17 digit number in a cell in Excel, the last 2 digits turn to >00 when I leave the cell. Format - Cell does not have a setting to stop this >'feature'. Excel Specifications and Limits: Calculation specifications Feature Maximu...

format cell #4
In Access, I can set up a field that "forces" the user to enter info - a date, for example - in a certain way, such as 25 Jan 05 or enter time as 12:15 AM. Is there a way that I can "force" this in excel? Thank you. Hello- Without invoking something more technical, you can select the cell(s) and go to Data>Validation and choose what type of entry be allowed in the field. Format the cell in the manner you wish to have the date or time expressed. HTH |:>) "HJC" wrote: > In Access, I can set up a field that "forces" the user to enter in...

Add a blank page separator
How do I add a blank page automatically as a separator after every 15 pages when printing a document of 250 pages? Use a macro containing the following code to print the document: Dim i As Long Dim blank As Document For i = 1 To 240 Step 15 ActiveDocument.PrintOut Range:=wdPrintFromTo, From:=i, To:=i + 15 Set blank = Documents.Add With blank .PrintOut .Close wdDoNotSaveChanges End With Next i -- Hope this helps, Doug Robbins - Word MVP Please reply only to the newsgroups unless you wish to obtain my services on a paid professio...

how can I add a signature to my calendar appt requests?
See subject question Insert-> Signature -- Robert Sparnaaij [MVP-Outlook] Coauthor, Configuring Microsoft Outlook 2003 http://www.howto-outlook.com/ Outlook FAQ, HowTo, Downloads, Add-Ins and more http://www.msoutlook.info/ Real World Questions, Real World Answers ----- "tnowak" <tnowak@discussions.microsoft.com> wrote in message news:51100433-5C23-435B-9C1B-5F8B8D9056B2@microsoft.com... > See subject question ...

Formatting cells and getting pound signs
I am using Excel 2003 with all updates as of 4/28/04 and trying to format a cell using the custom category and choosing the #,##0.00 type. I am trying to add the $ symbol at the beginning of the type and add text at the end of the type to look like this $#,##0.00 "text". When I do this however it shows up in my cell on my worksheet as ##########. It does know what the value is and shows as I would expect it to when I place mouse over cell in a balloon If I use only the $ symbol befor the type it shows fine. If I use only the "text" after the type is shows fine. Using the...

Find and replace with bold in cells
I have a VB6 program that is executing Excel 2007, opening a worksheet, and extracting some of the cells to write data to a text file. Some of the cells contain bold text on some (not necessarily all) of the text in the cell. I would like to do a find and replace on the bold tagging to replace it with something like "<b>" at the start of it and "</b>" at the end of it. How do I set this up in VB6? Thanks! The following function will return a string including <b> and </b> tags from the text of cell R. Function BoldMarkup(R As Range) As...

Selecting cell value for a sum, based on a condition
Trying to come up with a formula or method that will enable me to sum values based on a condition. For example, I have three columns which contain a condition and two amounts. If the condition is of the 'each' variety, one value will be used in the sum. If the condition is of the "square foot" variety, another value will be used. Here is a small diagram that may help visualize this: A B C D 1 Measure Unit Cost S.F. Cost Summed Total 2 Each 3.00 .30 3 S.F....

Conditional Formatting on cells beginning with a hyphen
Is it possible to do conditional formatting on cells beginning with a hyphen? Thanks, Greg 1. Place the cursor in A1 cell and select the Range 2. From menu Format>Conditional Formatting> 3. For Condition1>Select 'Formula Is' and paste the below formula =LEFT(A1,1)="-" 4. Click Format Button>Font>Color select your desired font & Background Color pattern and then give ok Change the cell reference of A1 to your desired cell, if required. But keep in mind that when applying the conditional formatting the Active cell should be in the ce...

Formula to find last monday (tue, wedn, thu or friday) for a given month
Hi, I need a formula to calculate the date of the last monday, tuesday, wednesday, thursday or friday of a given month. Can't seem to find the answer anywhere. example: day: wednesday (or corresponding nr) month: 3 year: 2004 Result: 31/03/04 Who can help? Thank you for reading and eventually answering my question.Back Visit http://www.cpearson.com/excel/DateTimeWS.htm#DaysInMonth -- Kind Regards, Niek Otten Microsoft MVP - Excel "Michele" <mw001@pandora.be> wrote in message news:b30b6913.0402090708.556d0faa@posting.google.com... > Hi, > I need a...

How do I make X-values of a chart dependent on values in cells?
Greetings. I have a chart which can go from x-value 0 to x-value 200. However I'd like to be able to input min X-value into a cell, and a max X-valu into a cell, and the x-value in the chart changes to reflect that. Is it possible to do that? Thanks for any replies. K -- Message posted from http://www.ExcelForum.com Hi, There is no automatic way to do this but take a look a Tushar's AutoChart Manager for a possible solution. (http://www.tushar-mehta.com/) Cheers Andy Kashgarinn < wrote: > Greetings. > > I have a chart which can go from x-value 0 to x-value 200...

modify linked cells without breaking link
I have a workbook with a number of worksheets (2003.) The 2nd and 3rd worksheets have cells that are linked to the 1st worksheet. This workbook will be used to schedule production. The 1st worksheet has a list of products that we produce. The 1st worksheet has a column for the min # of cases we need to keep in stock at all times and the max # of cases we must keep in stock at all times. The 1st worksheet also contains a column where the production scheduler would enter the actual # of cases in stock. The 2nd worksheet in the workbook takes the actual cs in stock and compares ...