Excel 2003: use number from text

[Two-eyes]

Hey all, long time no see =D

I'm trying to do something that seems impossible, but, IMO, Computerhope experts have a reputation of changing that ^.^

This is the scenario:
let's say I have a row (in Excel 2003, btw) such as this:
10|10|10|(exc) 5| etc

I would like to sum up those numbers, but because of that '(exc)' the 5 is ignored. Can I get it to be part of the sum, too, without removing the (exc)?

Another detail you might ask for: the 5 will always be after 6 characters, but the characters may differ (excluding, of course, human error ^.^)

Thanks very much,
Two-Eyes

[Salmon Trout]

the 5 will always be after 6 characters

There are 5 characters in the cell before the 5. Is this a "human error"? (What other kind is there?) (Just realised - are you counting a space as 1 character?)


(exc)
12345

[Salmon Trout]

Use the MID function to pick out the characters in the mixed cell which are after/to the right of the text. A separating space or spaces will be ignored.  In the formula in E1 the first number, 7, is 1 more than the number of chars in the string e.g. "(ext) " and the second number, 255, is any number which is will always be more than the number of characters in the number part.

Code: [Select]

A1 |  B1 | C1 |   D1    | E1
--------------------------------------------------
 10 |  10 | 10 | (exc) 5 | =A1+B1+C1+MID(D1,7,255)
E1 shows 35.

[soybean]

Another option for the formula: =A1+B1+C1+RIGHT(D1,1)

[Salmon Trout]

Another option for the formula: =A1+B1+C1+RIGHT(D1,1)

Only if the number is always going to be 1 digit, and if it is always the rightmost character.

[soybean]

You're quite right.  My impression from his post is the number will always be the rightmost character but he did not specifically state whether it would always be 1 digit.

[Two-eyes]

Hey there,

Very sorry for not replying.  I think soybean's is the better approach, since, it will always be one number on the right.

I was actually expecting more complex replies, from what I saw on other sites, but I'm glad MS's programmer's thought about me, too XD XD

Thank you very much,
Two-Eyes %

[Salmon Trout]

I think soybean's is the better approach

[soybean]

I think soybean's is the better approach