How to Delinate Text Strings by Multiple objects

M

Mstg007

Active Member
Joined
Dec 30, 2013
Messages
383
I have a file that I can open within Notepad. I want to be able to extract the data in a database form is possible but if not; a 2 cell split would be great.

The code is a from a calculation program. I would say it is similar to a XML file.
The idea is to extract the data from this file into a VLOOKUP table. I have another spreadsheet that has the data which is I want to replace that is in the code file. Hopefully, I can then do a find replace with the data. But that is the overall goal.



The below text string is code.

I need to be able to take:

"Field Name = ",0.0000000

And split this into 2 cells if possible.

Code: 
"Project Name = ",""
"SI Units? = ",#FALSE#
"Total No. Lines = ",6
"Starting HGL = ",0.00000000
"Return Period Index = ",2
"Min Cover = ",4.00000000
"Zero Min Cover at outfalls = ",#FALSE#
"Design Vel = ",3.00000000
"Min Slope = ",0.20000000
"Max Slope = ",10.00000000
"Min Pipe Size = ",12.00000000
"Max Pipe Size = ",96.00000000
"Default Pipe n-value = ",0.01200000
"Omit 21-inch pipes = ",#TRUE#
"Omit 27-inch pipes = ",#TRUE#
"Omit 33-inch pipes = ",#FALSE#
"Design Alignment = ",0
"Allow smaller downstream pipe sizes = ",#FALSE#
"N-Value of Inlets = ",0.01600000
"Grate Design Depth = ",0.30000000
"Composite C1 = ",0.20000000
"Composite C2 = ",0.50000000
"Composite C3 = ",0.90000000
"Min. Starting Depth = ",""
"Accumulate Known Qs = ",#TRUE#
"Use Inlet Captured Flows in System = ",#FALSE#
"Auto Compute Junct. Loss Coeff. = ",#TRUE#
"Supress Pipe Travel Time = ",#FALSE#
"Minimum Tc used to calc Intensity = ",5.00000000
"Check for Inlet Control? = ",#FALSE#
"Correct for EGL discrepancy = ",#FALSE#
"Using HDS-5 Method? = ",#FALSE#
"Curb Opening = ",""
LINE DATA =============================
"Network Name = ","PROPOSED STORM"
"Line No. = ",1
"Line ID = ","Pipe - (2)"
"Downstream Line No. = ",0
"X,Y Coord Dn = ",3158884.6365950620000,1650880.0170083879000
"X,Y Coord Up = ",3159012.9354670788000,1650866.0243591559000
"Deflection Angle = ",6.22424409
"Bearing = ",-173.77575591
"Known Q = ",0.00000000
"Sub Drainage Area 1 = ",0.00000000
"Sub Drainage Area 2 = ",0.00000000
"Sub Drainage Area 3 = ",0.00000000
"Drainage Area = ",0.00000000
"Runoff Coeff. = ",0.00000000
"Inlet Time = ",0.00000000
"Line Length = ",129.05965595
"Invert Elev Dn = ",835.63000000
"Line Slope = ",0.00240199
"Invert Elev Up = ",835.94000000
"Rise = ",1.25000000
"Span = ",1.25000000
"No. Barrels = ",1
"N-Value = ",0.01200000
"Line Type = ","Cir"
"Line Part ID = ","0A967E34-111D-4B25-AD18-52826357B9D4"
"Line Part Description = ","RCP"
"Junction Loss Coeff = ",0.00000000
"Ground / Rim Elev Dn = ",837.15083333
"Ground / Rim Elev Up = ",841.01459433
"Junction Type = ",6
"Junction Part ID = ","E7582C61-1376-4666-A4BB-4EBB49602B10"
"Junction Part Description = ","R-4342 Type C"
"User def. Sta = ",0.0000000000000,0.0000000000000
"User"
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
""
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
""
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
""
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
"HGL Down = ",0.00000000
"HGL Up = ",0.00000000
"HGL Junct = ",0.00000000
"EGL Down = ",0.00000000
"EGL Up = ",0.00000000
"EGL Junct = ",0.00000000
"Velocity Down = ",0.00000000
"Velocity Up = ",0.00000000
"Downstream Inlet No. = ",0
"Inlet Length = ",2.00000000
"Inlet throat height = ",0.00000000
"Grate Opening Area = ",0.00000000
"Grate Width = ",0.00000000
"Grate Length = ",2.00000000
"Known Capacity = ",0.00000000
"Gutter Width = ",0.00000000
"Gutter Slope = ",0.00000000
"Inlet Cross Slope Sw = ",0.00000000
"Inlet Cross Slope Sx = ",0.00000000
"Inlet Sag = ",0
"Inlet ID = ","#6"
"Local Inlet Depression = ",0.00000000
"Gutter N-Value = ",0.00000000
"Structure Shape = ","----"
"Structure Length = ",0.00000000
"Structure Width = ",0.00000000
"---------------------------------------"
"Line No. = ",2
"Line ID = ","Pipe - (3)"
"Downstream Line No. = ",1
"X,Y Coord Dn = ",3159012.9354670788000,1650866.0243591559000
"X,Y Coord Up = ",3159011.3383394405000,1650893.3320864579000
"Deflection Angle = ",-99.57144857
"Bearing = ",86.65279552
"Known Q = ",0.00000000
"Sub Drainage Area 1 = ",0.00000000
"Sub Drainage Area 2 = ",0.00000000
"Sub Drainage Area 3 = ",0.00000000
"Drainage Area = ",0.00000000
"Runoff Coeff. = ",0.00000000
"Inlet Time = ",0.00000000
"Line Length = ",27.35439246
"Invert Elev Dn = ",837.34000000
"Line Slope = ",0.01096716
"Invert Elev Up = ",837.64000000
"Rise = ",1.25000000
"Span = ",1.25000000
"No. Barrels = ",1
"N-Value = ",0.01200000
"Line Type = ","Cir"
"Line Part ID = ","0A967E34-111D-4B25-AD18-52826357B9D4"
"Line Part Description = ","RCP"
"Junction Loss Coeff = ",0.00000000
"Ground / Rim Elev Dn = ",841.01459433
"Ground / Rim Elev Up = ",841.66829747
"Junction Type = ",6
"Junction Part ID = ","8818D4C7-8ED9-4698-A5F0-7733F6D31222"
"Junction Part Description = ","R-3287-SB10 Type M"
"User def. Sta = ",0.0000000000000,0.0000000000000
"User"
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
""
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
""
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
""
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
"HGL Down = ",0.00000000
"HGL Up = ",0.00000000
"HGL Junct = ",0.00000000
"EGL Down = ",0.00000000
"EGL Up = ",0.00000000
"EGL Junct = ",0.00000000
"Velocity Down = ",0.00000000
"Velocity Up = ",0.00000000
"Downstream Inlet No. = ",0
"Inlet Length = ",0.00000000
"Inlet throat height = ",0.00000000
"Grate Opening Area = ",0.00000000
"Grate Width = ",0.00000000
"Grate Length = ",0.00000000
"Known Capacity = ",0.00000000
"Gutter Width = ",0.00000000
"Gutter Slope = ",0.00000000
"Inlet Cross Slope Sw = ",0.00000000
"Inlet Cross Slope Sx = ",0.00000000
"Inlet Sag = ",0
"Inlet ID = ","#5"
"Local Inlet Depression = ",0.00000000
"Gutter N-Value = ",0.00000000
"Structure Shape = ","----"
"Structure Length = ",0.00000000
"Structure Width = ",0.00000000
"---------------------------------------"
"Line No. = ",3
"Line ID = ","Pipe - (4)"
"Downstream Line No. = ",2
"X,Y Coord Dn = ",3159011.3383394405000,1650893.3320864579000
"X,Y Coord Up = ",3159091.2298372625000,1650895.3497486059000
"Deflection Angle = ",91.90050539
"Bearing = ",-181.44669909
"Known Q = ",0.00000000
"Sub Drainage Area 1 = ",0.00000000
"Sub Drainage Area 2 = ",0.00000000
"Sub Drainage Area 3 = ",0.00000000
"Drainage Area = ",0.00000000
"Runoff Coeff. = ",0.00000000
"Inlet Time = ",0.00000000
"Line Length = ",79.91697182
"Invert Elev Dn = ",837.74000000
"Line Slope = ",0.00400416
"Invert Elev Up = ",838.06000000
"Rise = ",1.25000000
"Span = ",1.25000000
"No. Barrels = ",1
"N-Value = ",0.01200000
"Line Type = ","Cir"
"Line Part ID = ","0A967E34-111D-4B25-AD18-52826357B9D4"
"Line Part Description = ","RCP"
"Junction Loss Coeff = ",0.00000000
"Ground / Rim Elev Dn = ",841.66829747
"Ground / Rim Elev Up = ",841.66635626
"Junction Type = ",6
"Junction Part ID = ","51380BD0-D6B4-45DF-AB9A-4C5CD4389354"
"Junction Part Description = ","R-3010 Type A"
"User def. Sta = ",0.0000000000000,0.0000000000000
"User"
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
""
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
""
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
""
0.00000000
0.00000000
0.00000000
0.00000000
0.00000000
"HGL Down = ",0.00000000
"HGL Up = ",0.00000000
"HGL Junct = ",0.00000000
"EGL Down = ",0.00000000
"EGL Up = ",0.00000000
"EGL Junct = ",0.00000000
"Velocity Down = ",0.00000000
"Velocity Up = ",0.00000000
"Downstream Inlet No. = ",0
"Inlet Length = ",0.00000000
"Inlet throat height = ",0.00000000
"Grate Opening Area = ",0.00000000
"Grate Width = ",0.00000000
"Grate Length = ",0.00000000
"Known Capacity = ",0.00000000
"Gutter Width = ",0.00000000
"Gutter Slope = ",0.00000000
"Inlet Cross Slope Sw = ",0.00000000
"Inlet Cross Slope Sx = ",0.00000000
"Inlet Sag = ",0
"Inlet ID = ","#4"
"Local Inlet Depression = ",0.00000000
"Gutter N-Value = ",0.00000000
"Structure Shape = ","----"
"Structure Length = ",0.00000000
"Structure Width = ",0.00000000
"---------------------------------------"
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
What are you splitting the cells based on ",?
 
Upvote 0
Yes I am trying to delinate them by the follow:

"what ever text = ", <---- in one cell

what is after it <------ in a cell next to it
 
Upvote 0
Have you tried using text to columns and using ", as the delimeter?
 
Upvote 0
I have tried the delimeter but I still need those ", to stay intact in the cell

example:

"Default Pipe n-value = ",0.01200000

cell one
"Default Pipe n-value = ",

cell two
0.01200000
 
Upvote 0
Since the equal sign seems to be consistent, what I would do is
1. Do the text to columns
2. Replace all = with =", you can press ctrl+H to replace all instances of the equal sign

That way your first cell would be fine and so would the second
 
Upvote 0
This will split the first comma it sees


Excel 2010
ABC
1"Default Pipe n-value = ",0.01200000"Default Pipe n-value = ",0.01200000
2"Project Name = ","""Project Name = ",""
3"SI Units? = ",#FALSE#"SI Units? = ",#FALSE#
4"Total No. Lines = ",6"Total No. Lines = ",6
5"Starting HGL = ",0.00000000"Starting HGL = ",0.00000000
6"Return Period Index = ",2"Return Period Index = ",2
7"Min Cover = ",4.00000000"Min Cover = ",4.00000000
8"Zero Min Cover at outfalls = ",#FALSE#"Zero Min Cover at outfalls = ",#FALSE#
9"Design Vel = ",3.00000000"Design Vel = ",3.00000000
Sheet5
Cell Formulas
RangeFormula
B1=LEFT(A1,SEARCH(",",A1))
B2=LEFT(A2,SEARCH(",",A2))
B3=LEFT(A3,SEARCH(",",A3))
B4=LEFT(A4,SEARCH(",",A4))
B5=LEFT(A5,SEARCH(",",A5))
B6=LEFT(A6,SEARCH(",",A6))
B7=LEFT(A7,SEARCH(",",A7))
B8=LEFT(A8,SEARCH(",",A8))
B9=LEFT(A9,SEARCH(",",A9))
C1=RIGHT(A1,(LEN(A1)-SEARCH(",",A1)))
C2=RIGHT(A2,(LEN(A2)-SEARCH(",",A2)))
C3=RIGHT(A3,(LEN(A3)-SEARCH(",",A3)))
C4=RIGHT(A4,(LEN(A4)-SEARCH(",",A4)))
C5=RIGHT(A5,(LEN(A5)-SEARCH(",",A5)))
C6=RIGHT(A6,(LEN(A6)-SEARCH(",",A6)))
C7=RIGHT(A7,(LEN(A7)-SEARCH(",",A7)))
C8=RIGHT(A8,(LEN(A8)-SEARCH(",",A8)))
C9=RIGHT(A9,(LEN(A9)-SEARCH(",",A9)))
 
Upvote 0
Sweet; Two things.

The cells that do not have the delimiter; is it possible to carry the value as is over just to the first cell not the second cell (within the spilt)
I import the calcs file into excel and goes into the first column A. Is there a way to have the values stop at the end of the column A and not go past it?
(reason; there could be a few lines or thousands of lines)
 
Upvote 0
@Shyy: in the event that the first comma is not the required one, what happens? :)

I modified shyys formula to suit your last request


Excel 2010
ABC
1"Junction Part Description = ","R-4342 Type C""Junction Part Description = ","R-4342 Type C"
2"User def. Sta = ",0.0000000000000,0.0000000000000"User def. Sta = ",0.0000000000000,0.0000000000000
3"User""User" 
Sheet30
Cell Formulas
RangeFormula
B1=IFERROR(LEFT(A1,SEARCH(",",A1)),A1)
B2=IFERROR(LEFT(A2,SEARCH(",",A2)),A2)
B3=IFERROR(LEFT(A3,SEARCH(",",A3)),A3)
C1=IF(A1=B1,"",RIGHT(A1,(LEN(A1)-SEARCH(",",A1))))
C2=IF(A2=B2,"",RIGHT(A2,(LEN(A2)-SEARCH(",",A2))))
C3=IF(A3=B3,"",RIGHT(A3,(LEN(A3)-SEARCH(",",A3))))
 
Last edited:
Upvote 0
Better to use this one, this will search for ", instead of just a comma


Excel 2010
ABC
1"Default Pipe n-value = ",0.01200000"Default Pipe n-value = ",0.01200000
2"Project Name = ","""Project Name = ",""
3"SI Units? = ",#FALSE#"SI Units? = ",#FALSE#
4"Total No. Lines = ",6"Total No. Lines = ",6
5"Starting HGL = ",0.00000000"Starting HGL = ",0.00000000
6"Return Period Index = ",2"Return Period Index = ",2
7"Min Cover = ",4.00000000"Min Cover = ",4.00000000
8"Zero Min Cover at outfalls = ",#FALSE#"Zero Min Cover at outfalls = ",#FALSE#
9"Design Vel = ",3.00000000"Design Vel = ",3.00000000
10blahblah 
11"Max Slope = ",10.00000000"Max Slope = ",10.00000000
12"Min Pipe Size = ",12.00000000"Min Pipe Size = ",12.00000000
13"Max Pipe Size = ",96.00000000"Max Pipe Size = ",96.00000000
Sheet5
Cell Formulas
RangeFormula
B1=IFERROR(LEFT(A1,SEARCH(""",",A1)+1),"")
B2=IFERROR(LEFT(A2,SEARCH(""",",A2)+1),"")
B3=IFERROR(LEFT(A3,SEARCH(""",",A3)+1),"")
B4=IFERROR(LEFT(A4,SEARCH(""",",A4)+1),"")
B5=IFERROR(LEFT(A5,SEARCH(""",",A5)+1),"")
B6=IFERROR(LEFT(A6,SEARCH(""",",A6)+1),"")
B7=IFERROR(LEFT(A7,SEARCH(""",",A7)+1),"")
B8=IFERROR(LEFT(A8,SEARCH(""",",A8)+1),"")
B9=IFERROR(LEFT(A9,SEARCH(""",",A9)+1),"")
B11=IFERROR(LEFT(A11,SEARCH(""",",A11)+1),"")
B12=IFERROR(LEFT(A12,SEARCH(""",",A12)+1),"")
B13=IFERROR(LEFT(A13,SEARCH(""",",A13)+1),"")
C1=IF(A1=B1,"",RIGHT(A1,(LEN(A1)-SEARCH(""",",A1)-1)))
C2=IF(A2=B2,"",RIGHT(A2,(LEN(A2)-SEARCH(""",",A2)-1)))
C3=IF(A3=B3,"",RIGHT(A3,(LEN(A3)-SEARCH(""",",A3)-1)))
C4=IF(A4=B4,"",RIGHT(A4,(LEN(A4)-SEARCH(""",",A4)-1)))
C5=IF(A5=B5,"",RIGHT(A5,(LEN(A5)-SEARCH(""",",A5)-1)))
C6=IF(A6=B6,"",RIGHT(A6,(LEN(A6)-SEARCH(""",",A6)-1)))
C7=IF(A7=B7,"",RIGHT(A7,(LEN(A7)-SEARCH(""",",A7)-1)))
C8=IF(A8=B8,"",RIGHT(A8,(LEN(A8)-SEARCH(""",",A8)-1)))
C9=IF(A9=B9,"",RIGHT(A9,(LEN(A9)-SEARCH(""",",A9)-1)))
C10=IF(A10=B10,"",RIGHT(A10,(LEN(A10)-SEARCH(""",",A10)-1)))
C11=IF(A11=B11,"",RIGHT(A11,(LEN(A11)-SEARCH(""",",A11)-1)))
C12=IF(A12=B12,"",RIGHT(A12,(LEN(A12)-SEARCH(""",",A12)-1)))
C13=IF(A13=B13,"",RIGHT(A13,(LEN(A13)-SEARCH(""",",A13)-1)))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
If macro, when keyword phrase is in certain cell
Replies
2
Views
309
rcb007
R

Forum statistics

Threads
1,215,029
Messages
6,122,755
Members
449,094
Latest member
dsharae57

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top