Adding specific values

Hi guys. I have managed to get my data mainly numerical, but for a couple of cells I need some text. This doesnt work with pivot tables, so I thought I would try and extract the numbers. So I have a cell which contains the data

7 pax 2 cars @ 12.05
2 pax 1 car @13.45

So the cell has a line break and contains 2 lines. I essentially need to add the first number of each line, allowing for more lines to be added. So I have this to get the first number from the first line

Please Login or Register  to view this content.

I then have this formula which tells me how many lines there are

Please Login or Register  to view this content.

Is there anyway I could maybe combine the two to add the first number of every line? So for the above, it should be 7+2 so return 9.

Thanks

Kool, that seems to work perfectly. That seems to work for 2 lines, say if I had 3 and I needed to extend the formula, is it a case of just duplicating the find part? Going through it now so I can fully understand it.

Cheers

Hey, I have just noticed a little problem. That formula counts the 2 numbers on the first line. What I am trying to do is count the first number on the first line, and the first number on the second line. So if its

7(this number) pax 4 cars @ 12.05
2(+ this number) pax 1 car @ 13.45

The above would be 7+2 = 9

Is that possible?

Cheers

Ah, I see where you are heading. So you are finding the first space and then going to the character before this. The only problem with the formula is that it presumes the two lines are in two separate cells. Because I am working in a table format, the two lines are in the same cell. So I essentially need the first part to refer to A1 line 1, and the second part to refer to A1 line 2.

Cheers

I have been using Alt + Enter to create new lines and your formula works perfectly!! If only I could give you more rep!!! Thanks for the help, I have learned a lot. Will mark this thread as solved now, thanks.

Nick