extract elements of strings

[SteveDB1]

Morning all.
While I've asked on this general topic before and obtained the solution I
sought, this is different in that I've now used AutoCAD 2009's dataextraction
tool and the text I chose within AutoCAD was a "multi-text."

In doing so I've found that the output into excel resembles the following.

'xxx/Pxxxx/Pxxxx/Pxxxx/P............./Pxxxx
where the x's are a series of numeric values I need to pull and use for my
spreadsheet.

here's an actual sample.

'4.75\P5.05\P0.16\P5.30\P1.19\P1.01\P1.01\P1.01\P1.00\P2.96\P1.00\P1.00\P0.96\P0.95\P1.99\P1.00\P1.21\P1.00\P1.00\P1.00\P2.10\P2.85\P2.10\P4.92\P6.50\P2.33\P2.61\P1.00\P1.00\P1.00\P0.99\P1.00\P2.07\P3.13\P1.00\P1.00\P1.00\P1.00\P1.00\P0.89\P2.55\P\P76.60\P

and the other portion of the sample is-- in its own column:

'09-120-01\P09-120-05\P09-120-15\P09-120-22\P09-120-26\P09-120-27\P09-120-28\P09-120-29\P09-120-44\P09-120-45\P09-120-46\P09-120-52\P09-120-53\P09-120-54\P09-120-56\P09-120-57\P09-131-40\P09-131-41\P09-131-42\P09-131-43\P09-131-47\P09-131-49\P09-131-50\P09-132-08\P09-132-13\P09-132-18\P09-132-19\P09-132-29\P09-132-30\P09-132-31\P09-132-34\P09-132-35\P09-132-36\P09-132-37\P09-132-38\P09-132-39\P09-132-40\P09-132-42\P09-132-43\PBasque
Ln.\PJuniper Hill Rd.\P\PTotal\P

I'm looking to extract the numbers/text and place each grouping separated by
the /P's in its own cell, in a single column. In the case of the samples
above, that'd be two columns of data.

How would I accomplish this?
Thank you.
Best.
SteveB.

 

[Gary''s Student]

If A1 contains:

4.75\P5.05\P0.16\P5.30\P1.19\P1.01\P1.01\P1.01\P1.00\P2.96\P1.00\P1.00

Select A1 and run this macro:

Sub steve()
v = ActiveCell.Value
s = Split(v, "\P")
For i = LBound(s) To UBound(s)
ActiveCell.Offset(i + 1, 0).Value = s(i)
Next
End Sub

Cells A2 thru A13 will contain:

4.75
5.05
0.16
5.3
1.19
1.01
1.01
1.01
1
2.96
1
1

 

[SteveDB1]

Perfect-- Gary, thank you.
Yet another satisfied customer.... ;-)

 

[Gary''s Student]

Thanks for the feedback!