Counting pages and lines (added issue)

Hey, I've been having some difficulty getting this done so i figured id ask for help.

Im trying to create a formula where all i need to do is input the starting page and line number then the ending page and line number and get a total line count.

For example

starting page number:line number - ending page number:line number. Each page has 25 lines, and the first line has to be counted.

5:9 - 5:22 = 14 lines

A more complicated one would be

10:15 - 12:1 = 37 lines. (11 lines in page 10, 25 lines in page 11, and 1 line in page 12).

Any ideas or thoughts would be great. Thanks.

Assuming that you set up your nominclature just as you described in A1 and A2 and that those cells are formatted as text (otherwise you'll get some registering as time).

=LEFT(A2,FIND(":",A2)-1)*25-LEFT(A1,FIND(":",A1)-1)*25+MID(A2,FIND(":",A2)+1,2)-MID(A1,FIND(":",A1)+1,2)+1

Does that work for you?

Ok, i partially figured it out.

Taking it a step further.

Is there anyway to get it done if the entire page range is in one cell?
Im converting a PDF to excel and it takes the entire column as is, so id have to manually split up thousands of start and end pages.

It converts it to one cell and it looks like this - 5:9 - 5:22 in A1, A2 is 9:7 - 9:21, A3 is 10:15 - 12:1, and so on all the way down.

Can you upload an example of those cells? Spacing will be crutial in separating them. Attaches is the spreadsheet for my first example.

Okay, this could be done in a single cell with a very long formula but easier to use some intermediate columns and work it that way. i.e. separate your first and last rows first into columns B and C, then do the calculation.

In B1
=TRIM(LEFT(A1,FIND("–",A1)-2))

In C1
=TRIM(MID(A1,FIND("–",A1)+2,5)) Note: 5 should be the maximum length unless you plan on going over 100 pages, then replace with a 6

In D1
=LEFT(C1,FIND(":",C1)-1)*25-LEFT(B1,FIND(":",B1)-1)*25+MID(C1,FIND(":",C1)+1,2)-MID(B1,FIND(":",B1)+1,2)+1

See attachment.
Does that work for you?