Prev: Hyperlinks for .rm files stopped working
Next: How do I maintain format in programs.
From: mgbcab on 24 May 2010 08:03
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
> > .
> >
From: T. Valko on 24 May 2010 09:40
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


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


First  |  Prev  | 
Pages: 1 2 3 4
Prev: Hyperlinks for .rm files stopped working
Next: How do I maintain format in programs.