How to Delinate Text Strings by Multiple objects

[Mstg007]

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.

"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
"---------------------------------------"

 

[Mstg007]

I am assuming I can not upload the xls for you to review it. But it seems to be working. So, I can easily spilt the data. Is there a way I can do a vlookup within all this data?

Example:

Within the "Line No. = ",1 <---- This is where the Block of Data Starts

"Invert Elev Up = ",-6.45530000 <------ Column A

"Invert Elev Up = ", <----- Column B

-6.4553000 <----- Column C

"---------------------------------------" <----- This is where the Block of Data Ends (Always 95 Lines)

I would like in another cell to have a revised value fr the Invert Elev Up to be 800.00

 

[Momentman]

Sometimes, one has issues getting the exact number of quotes to put within the search command, when there's a quote within the string you seek. One could use it this way where char(34) is a double quote =SEARCH(char(34) & ",",A1)

 

[shyy]

interesting never thought about using the CHAR function

 

[Mstg007]

I guess maybe help clear it up more.
If I have C5 Cell to be titled Line No.
D5 Cell to be titled Invert Elev Down
C6 would have 1
D6 would have 800.00
C7 would have 5
D7 would have 810.10
C8 would have 10
D8 would have 822.22

Could it search and replace those vaules from the split cells we just extracted?

 

[shyy]

as long as the values are split, you can vlookup from column A

=VLOOKUP(IFERROR(LEFT(A3,SEARCH(""",",A3)+1),""),$F$3:$H$4,2,FALSE)

 

[Rick Rothstein]

Better to use this one, this will search for ", instead of just a comma

Excel 2010

	A	B	C
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
10	blah	blah
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

<colgroup><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas

Cell	Formula
B1	=IFERROR(LEFT(A1,SEARCH(""",",A1)+1),"")
C1	=IF(A1=B1,"",RIGHT(A1,(LEN(A1)-SEARCH(""",",A1)-1)))

<thead>
</thead><tbody>
</tbody>

Here is a more compact formula (two function calls versus your four) that can be used in cell C1 instead...

=REPLACE(A1,1,FIND(""",",A1&""",")+1,"")