Increment a cell value every 23rd line

Question

I have the statement below at every 23rd line in an excel sheet CSV format (there are no formulae in the document, I just assumed using excel would be the easiest way to change this!)

wp_pos=(13.0479,0.100,-165.4428) - the values are always different

I want to change every occurence of the 2nd value, (in this instance 0.100)

I want to change it to a total value of 'cell value + 0.200 (i.e. 0.30)

If the document is put into excel then line

Cell A is wp_pos=(13.0479,0.100)

Cell B is 0.100 <-- it's this value I wish to change

How do I do it for every 23rd line?

Answer 1

If cell A contains wp_pos=(13.0479,0.100,-165.4428) then the first step is to split the values out. So select column A and go to Data/Text to Columns. Choose comma the delimeters.

Now you will have

A = wp_pos=(13.0479
B = 0.100
C = -165.4428)

In column D1, put

=if(mod(row(),23)=0,B1+.2,B1)

This will check if the row number is divisible by 23 exactly, and if so, D1 will contain B1 + .2, otherwise it will contain B1

In E1 put

=concatenate(A1,",",D1,",",C1)

This will join everything back together again. Copy D1 and E1 down for the length of your data

You can now copy column E1 and paste special (values) into a new sheet, so you just get formulas, then export.

Answer 2

My proposal below assumes wp_pos=(13.0479,0.1,-165.4428) in column A will be replaced with wp_pos=(13.0479,0.3,-165.4428) in the same column (wherever "wp_pos" occurs in this column) but having re-read the question and comments this may not be the requirement. In which case.. ignore me!

I have a slightly complicated formula - don't know if it is of interest:

=REPLACE(A4,SEARCH(",",A4,1)+1,SEARCH(",",A4,SEARCH(",",A4,1)+1)-SEARCH(",",A4,1)-1,TEXT(VALUE(MID(A4,SEARCH(",",A4,1)+1,SEARCH(",",A4,SEARCH(",",A4,1)+1)-'SEARCH(",",A4,1)-1)+0.2),"@"))

Added: Slightly simplified version:

=REPLACE(A4,SEARCH(",",A4,1)+1,SEARCH(",",A4,SEARCH(",",A4,1)+1)-SEARCH(",",A4,1)-1,MID(A4,SEARCH(",",A4,1)+1,SEARCH(",",A4,SEARCH(",",A4,1)+1)-SEARCH(",",A4,1)-1)+0.2)

I assume it could, possibly, be simplified further.

I would approach inserting these formulas in a slightly different way, but I'm assuming that wp_pos= is a criteria we can use? Rather than every 23rd row. If not, then ignore me again!

Anyway, in case it is of interest, but I apologise that it is a bit elaborate:

• It the data doesn't already have column headings, create a dummy heading row. Just insert it at the top, type Heading1 and drag this to the right. Drag one column further than the last occupied column.
• Use AutoFilter and for the first column, choose Custom Filter and 'begins with', type 'wp_pos`.
• Click the first data-cell (of the first column) and press Ctrl-Shift-Down to highlight to the bottom.
• Press Ctrl-Shift-Right to extend the selection to the end.
• Shift-Right to include the blank column.
• Press Ctrl . (period) to make the cell at the top-right corner the active cell.
• Ctrl-Shift-Right to collapse the selection to just this column. You may have to press this a couple of times, until only this blank column is selected.
• Type the formula into the active cell, changing A4 to whichever is the current row number.
• Press Ctrl-Enter to fill this formula into the selected cells.

You can't Copy/Paste-Special, Values with the current selection. So you need to remove the filter, select this new column, then Copy/Paste-Special, Values (over the same column). Then copy the same column again, select the first column. Right-click to find Paste-Special and click the option to skip blanks. The original wp_pos cells will be replaced, but the others will remain intact.

As I said, elaborate! But this outline may contain some useful information (for someone..).