Prev: Hyperlinks for .rm files stopped working
Next: How do I maintain format in programs.
From: Ron Rosenfeld on 22 May 2010 11:40
On Sat, 22 May 2010 10:30:42 -0400, "T. Valko" <biffinpitt(a)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
From: Ron Rosenfeld on 22 May 2010 11:44
On Sat, 22 May 2010 04:36:01 -0700, mgbcab <mgbcab(a)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
From: T. Valko on 22 May 2010 12:12
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(a)nospam.org> wrote in message
news:s1ufv599ddlc6n3799vtv8hea0pm4476lr(a)4ax.com...
> On Sat, 22 May 2010 10:30:42 -0400, "T. Valko" <biffinpitt(a)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


From: Ron Rosenfeld on 22 May 2010 12:42
On Sat, 22 May 2010 12:12:59 -0400, "T. Valko" <biffinpitt(a)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
From: bala_vb on 22 May 2010 13:56

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  |  Next  |  Last
Pages: 1 2 3 4
Prev: Hyperlinks for .rm files stopped working
Next: How do I maintain format in programs.