divide a string words separated by (number)

F

ferde

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
 
D

Don Guillett

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
 
R

Ron Rosenfeld

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))
 
F

ferde

Thank you for the help

Don Guillett said:
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
(e-mail address removed)


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top