| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
extract elements of strings
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.2 1\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\P7 6.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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
extract elements of strings
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 -- Gary''s Student - gsnu200806 "SteveDB1" wrote: 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.2 1\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\P7 6.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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
extract elements of strings
Perfect-- Gary, thank you.
Yet another satisfied customer.... ;-) "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200806 "SteveDB1" wrote: 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.2 1\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\P7 6.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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
extract elements of strings
Thanks for the feedback!
-- Gary''s Student - gsnu200806 "SteveDB1" wrote: Perfect-- Gary, thank you. Yet another satisfied customer.... ;-) "Gary''s Student" wrote: 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 -- Gary''s Student - gsnu200806 "SteveDB1" wrote: 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.2 1\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\P7 6.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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract top 5 elements from table | Excel Programming | |||
| read contents of col 1 and extract similar strings | Excel Discussion (Misc queries) | |||
| HOW TO EXTRACT STRINGS FROM CELLS | Excel Discussion (Misc queries) | |||
| How do I find the number of elements in an Array of Strings? | Excel Programming | |||
| Extract numbers from strings | Excel Worksheet Functions | |||
Linear Mode
