Prev: Automatically update worksheet name
Next: Filtering data based on historical values
From: ferde on 3 Jun 2010 17:46
I would like to have the data that is in one cell parsed into two cells like
in the example below. The data is always separated by a consecutive whole
number in parenthesis . Thank you
A
(1.) White Dog (2.) Black Cat


A B
White Dog Black Cat
From: Don Guillett on 3 Jun 2010 18:50
Sub SplitCellswith()
For Each n In Range("h2:h6")
c = Application.Substitute(n, " ", "")
p1 = InStr(1, Trim(c), ")")
p2 = InStr(p1 + 1, c, ")") + 1
p3 = InStr(p1 + 1, c, "(") - 1
n.Offset(, 1) = Mid(c, p2, 256)
n.Offset(, 0) = Mid(c, p1 + 1, p3 - p1)
Next n
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"ferde" <ferde(a)discussions.microsoft.com> wrote in message
news:4280DC4A-0EB1-4403-9979-26D7D0117D50(a)microsoft.com...
>I would like to have the data that is in one cell parsed into two cells
>like
> in the example below. The data is always separated by a consecutive whole
> number in parenthesis . Thank you
> A
> (1.) White Dog (2.) Black Cat
>
>
> A B
> White Dog Black Cat

From: Ron Rosenfeld on 3 Jun 2010 20:08
On Thu, 3 Jun 2010 14:46:26 -0700, ferde
<ferde(a)discussions.microsoft.com> wrote:

>I would like to have the data that is in one cell parsed into two cells like
>in the example below. The data is always separated by a consecutive whole
>number in parenthesis . Thank you
> A
>(1.) White Dog (2.) Black Cat
>
>
>A B
>White Dog Black Cat

B1:
=TRIM(MID(A1,FIND(")",A1)+1,FIND("(",A1,FIND("(",A1)+1)-FIND(")",A1)-1))

C1:
=TRIM(MID(A1,FIND(")",A1,FIND(")",A1)+1)+1,255))

From: ferde on 3 Jun 2010 20:33
Thank you for the help

"Don Guillett" wrote:

> Sub SplitCellswith()
> For Each n In Range("h2:h6")
> c = Application.Substitute(n, " ", "")
> p1 = InStr(1, Trim(c), ")")
> p2 = InStr(p1 + 1, c, ")") + 1
> p3 = InStr(p1 + 1, c, "(") - 1
> n.Offset(, 1) = Mid(c, p2, 256)
> n.Offset(, 0) = Mid(c, p1 + 1, p3 - p1)
> Next n
> End Sub
>
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> dguillett(a)gmail.com
> "ferde" <ferde(a)discussions.microsoft.com> wrote in message
> news:4280DC4A-0EB1-4403-9979-26D7D0117D50(a)microsoft.com...
> >I would like to have the data that is in one cell parsed into two cells
> >like
> > in the example below. The data is always separated by a consecutive whole
> > number in parenthesis . Thank you
> > A
> > (1.) White Dog (2.) Black Cat
> >
> >
> > A B
> > White Dog Black Cat
>
> .
>
 | 
Pages: 1
Prev: Automatically update worksheet name
Next: Filtering data based on historical values