Prev: Hyperlinks for .rm files stopped working
Next: How do I maintain format in programs.
From: mgbcab on 22 May 2010 07:36
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.
From: p45cal on 22 May 2010 09:41

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


--------------------







m
g
b
c
a
b
;
7
2
9
5
5
1

W
r
o
t
e
:


>
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

From: T. Valko on 22 May 2010 10:30
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(a)discussions.microsoft.com> wrote in message
news:33346750-6F48-419E-8CC0-4E263A2BD8E4(a)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.


From: p45cal on 22 May 2010 11:14

*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

From: T. Valko on 22 May 2010 11:24
>though here's hoping...

Which is why I included a disclaimer:

>...the sample data you posted.

--
Biff
Microsoft Excel MVP


"p45cal" <p45cal.4bdctx(a)thecodecage.com> wrote in message
news:p45cal.4bdctx(a)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
>


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