Formula Based Find Replace - Thousands of lines of code

M

mco5044

Board Regular
Joined
Jul 23, 2012
Messages
51
I was 3d printing an object and 10 hours into it the base came off and the project was ruined. I was able to locate in the printer instructions exactly which layer the error occurred at so now I am able to edit the instructions file so I can only print the remaining piece.

So I deleted the previous data in the file and I'm on the correct layer. However the Z (height) of the nozzle part of the instructions need to be modified so when I print the remaining part of the piece it starts on the base instead of starting in the air.

That's the background story. If you don't understand that, no worries. What I need is to have a formula that can replace text within text based on an incremental scale.

Here is some example rows of the file I pasted into excel


G0 X99.761 Y96.336
G0 X100.065 Y96.661
G0 X101.465 Y98.967
G0 X102.205 Y100.605
G0 X96.761 Y101.122
;TIME_ELAPSED:35185.445884
;LAYER:434
G0 X96.761 Y101.122 Z0.2
M204 S500
M205 X20 Y20
;TYPE:FILL
G1 F3600 X99.043 Y100.511 E3085.42512
G1 X99.139 Y100.486
M204 S5000


See right under Layer 434 there is a line that says Z0.2 at the end of it?

Imagine 5,000 rows down there is layer 435 and underneath there is the Z value again. We need a formula to change that Z value to Z0.4. Then another 5,000 rows down we see layer 436 and we want that Z value to be 0.6 and so on.

In all the lines of text between each Z value the letter Z never appears. So it is easy to find each Z value. It's the part where we find whatever it is and then replace it with the previous Z + .2 that I can't figure out.

I would manually do this in find and replace, but there are hundreds of rows I need to update so hoping someone has a formula to tackle this issue

Thank you all for your help!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
Every row on the example text is in one cell only. So think of just a giant file pasted into Column A going down the sheet
 
Upvote 0
Maybe a VBA solution....as long as Z0.2 only occurs in those locations, as this will change ALL occurences

Code: 
Sub MM1()
  With Range("A1", Cells(Rows.Count, "A").End(xlUp))
    .Replace "Z0.2", "Z0.4", xlPart, , False, , False, False
  End With
End Sub
 
Upvote 0
Hi,

Assuming your Z value is Always at the end of the Text string within the cell, try and see if this accomplishes what you need:


Book1
AB
1G0 X99.761 Y96.336G0 X99.761 Y96.336
2G0 X100.065 Y96.661G0 X100.065 Y96.661
3G0 X101.465 Y98.967G0 X101.465 Y98.967
4G0 X102.205 Y100.605G0 X102.205 Y100.605
5G0 X96.761 Y101.122G0 X96.761 Y101.122
6;TIME_ELAPSED:35185.445884;TIME_ELAPSED:35185.445884
7;LAYER:434;LAYER:434
8G0 X96.761 Y101.122 Z0.2G0 X96.761 Y101.122 Z0.2
9M204 S500M204 S500
10M205 X20 Y20M205 X20 Y20
11;TYPE:FILL;TYPE:FILL
12G1 F3600 X99.043 Y100.511 E3085.42512G1 F3600 X99.043 Y100.511 E3085.42512
13G1 X99.139 Y100.486G1 X99.139 Y100.486
14M204 S5000M204 S5000
15;LAYER:435;LAYER:435
16G0 X96.761 Y101.122 Z0.2G0 X96.761 Y101.122 Z0.4
17;LAYER:436;LAYER:436
18G0 X96.761 Y101.122 Z0.3G0 X96.761 Y101.122 Z0.6
19;LAYER:437;LAYER:437
20G0 X96.761 Y101.122 Z0.4G0 X96.761 Y101.122 Z0.8
Sheet380
Cell Formulas
RangeFormula
B1=IFERROR(REPLACE(A1,FIND("Z0.",A1),255,"Z0."&COUNTIF(A$1:A1,"*LAYER:*")*2),A1)


Formula copied down as far as needed.

EDIT: realize I might have mis-understood the problem, will post back with Updated Formula.
 
Last edited:
Upvote 0
Updated formula below, copied down as far as needed.


Book1
AB
1G0 X99.761 Y96.336G0 X99.761 Y96.336
2G0 X100.065 Y96.661G0 X100.065 Y96.661
3G0 X101.465 Y98.967G0 X101.465 Y98.967
4G0 X102.205 Y100.605G0 X102.205 Y100.605
5G0 X96.761 Y101.122G0 X96.761 Y101.122
6;TIME_ELAPSED:35185.445884;TIME_ELAPSED:35185.445884
7;LAYER:434;LAYER:434
8G0 X96.761 Y101.122 Z0.2G0 X96.761 Y101.122 Z0.2
9M204 S500M204 S500
10M205 X20 Y20M205 X20 Y20
11;TYPE:FILL;TYPE:FILL
12G1 F3600 X99.043 Y100.511 E3085.42512G1 F3600 X99.043 Y100.511 E3085.42512
13G1 X99.139 Y100.486G1 X99.139 Y100.486
14M204 S5000M204 S5000
15;LAYER:435;LAYER:435
16G0 X96.761 Y101.122 Z0.3G0 X96.761 Y101.122 Z0.4
17;LAYER:436;LAYER:436
18G0 X96.761 Y101.122 Z0.4G0 X96.761 Y101.122 Z0.6
19;LAYER:437;LAYER:437
20G0 X96.761 Y101.122 Z0.5G0 X96.761 Y101.122 Z0.8
21;LAYER:438;LAYER:438
22G0 X96.761 Y101.122 Z0.6G0 X96.761 Y101.122 Z1
23;LAYER:439;LAYER:439
24G0 X96.761 Y101.122 Z0.7G0 X96.761 Y101.122 Z1.2
25;LAYER:440;LAYER:440
26G0 X96.761 Y101.122 Z0.8G0 X96.761 Y101.122 Z1.4
27;LAYER:441;LAYER:441
28G0 X96.761 Y101.122 Z0.9G0 X96.761 Y101.122 Z1.6
29;LAYER:442;LAYER:442
30G0 X96.761 Y101.122 Z1G0 X96.761 Y101.122 Z1.8
31;LAYER:443;LAYER:443
32G0 X96.761 Y101.122 Z1.1G0 X96.761 Y101.122 Z2
33;LAYER:444;LAYER:444
34G0 X96.761 Y101.122 Z1.2G0 X96.761 Y101.122 Z2.2
Sheet380
Cell Formulas
RangeFormula
B1=IFERROR(REPLACE(A1,FIND("Z",A1),255,"Z"&COUNTIF(A$1:A1,"*LAYER:*")*2/10),A1)
 
Upvote 0
mco5044 said:
Imagine 5,000 rows down there is layer 435 and underneath there is the Z value again.
Click to expand...
Let's say layer 434 is in cell A1, do you mean layer 435 is exactly in cell A5001?
What is the Z value underneath layer 435 (also layer 436,437 etc)? is it Z0.2?
Are you ok to use vba?
 
Last edited:
Upvote 0
Michael M said:
@jtakw
Me too
Click to expand...

Yeah, my original formula in Post # 4 will Error out after Z0.5 for the original text string, formula in Post # 5 "should" work for OP.

@ OP

To quickly copy the formula down your Large Set of data:
Assuming your data starts at A1.
1. Copy my formula in Post # 5 in B1
2. With B1 selected, Double Left click on the Cell Fill Handle ( small square at lower right corner of B1 ), formula is now copied down Column B to the End of your data in corresponding Column A.
 
Upvote 0
jtakw said:
Updated formula below, copied down as far as needed.

AB
G0 X99.761 Y96.336G0 X99.761 Y96.336
G0 X100.065 Y96.661G0 X100.065 Y96.661
G0 X101.465 Y98.967G0 X101.465 Y98.967
G0 X102.205 Y100.605G0 X102.205 Y100.605
G0 X96.761 Y101.122G0 X96.761 Y101.122
;TIME_ELAPSED:35185.445884;TIME_ELAPSED:35185.445884
;LAYER:434;LAYER:434
G0 X96.761 Y101.122 Z0.2G0 X96.761 Y101.122 Z0.2
M204 S500M204 S500
M205 X20 Y20M205 X20 Y20
;TYPE:FILL;TYPE:FILL
G1 F3600 X99.043 Y100.511 E3085.42512G1 F3600 X99.043 Y100.511 E3085.42512
G1 X99.139 Y100.486G1 X99.139 Y100.486
M204 S5000M204 S5000
;LAYER:435;LAYER:435
G0 X96.761 Y101.122 Z0.3G0 X96.761 Y101.122 Z0.4
;LAYER:436;LAYER:436
G0 X96.761 Y101.122 Z0.4G0 X96.761 Y101.122 Z0.6
;LAYER:437;LAYER:437
G0 X96.761 Y101.122 Z0.5G0 X96.761 Y101.122 Z0.8
;LAYER:438;LAYER:438
G0 X96.761 Y101.122 Z0.6G0 X96.761 Y101.122 Z1
;LAYER:439;LAYER:439
G0 X96.761 Y101.122 Z0.7G0 X96.761 Y101.122 Z1.2
;LAYER:440;LAYER:440
G0 X96.761 Y101.122 Z0.8G0 X96.761 Y101.122 Z1.4
;LAYER:441;LAYER:441
G0 X96.761 Y101.122 Z0.9G0 X96.761 Y101.122 Z1.6
;LAYER:442;LAYER:442
G0 X96.761 Y101.122 Z1G0 X96.761 Y101.122 Z1.8
;LAYER:443;LAYER:443
G0 X96.761 Y101.122 Z1.1G0 X96.761 Y101.122 Z2
;LAYER:444;LAYER:444
G0 X96.761 Y101.122 Z1.2G0 X96.761 Y101.122 Z2.2

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

[TD="align: center"]14[/TD]

[TD="align: center"]15[/TD]

[TD="align: center"]16[/TD]

[TD="align: center"]17[/TD]

[TD="align: center"]18[/TD]

[TD="align: center"]19[/TD]

[TD="align: center"]20[/TD]

[TD="align: center"]21[/TD]

[TD="align: center"]22[/TD]

[TD="align: center"]23[/TD]

[TD="align: center"]24[/TD]

[TD="align: center"]25[/TD]

[TD="align: center"]26[/TD]

[TD="align: center"]27[/TD]

[TD="align: center"]28[/TD]

[TD="align: center"]29[/TD]

[TD="align: center"]30[/TD]

[TD="align: center"]31[/TD]

[TD="align: center"]32[/TD]

[TD="align: center"]33[/TD]

[TD="align: center"]34[/TD]

</tbody>
Sheet380​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IFERROR(REPLACE(A1,FIND("Z",A1),255,"Z"&COUNTIF(A$1:A1,"*LAYER:*")*2/10),A1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...

You sir are a god **** genius!

This file was about 680K lines. Your formula was exactly what I was looking for and saved me tons of time!

Thank you very much
 
Upvote 0
You're welcome, glad it worked for you.
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,326
Members
452,635
Latest member
laura12345

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