Computer Hope

Software => Computer software => Topic started by: Two-eyes on April 24, 2010, 11:42:24 AM

Title: Excel 2003: use number from text
Post by: Two-eyes on April 24, 2010, 11:42:24 AM: 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
Title: Re: Excel 2003: use number from text
Post by: Salmon Trout on April 24, 2010, 01:52:53 PM: Quote
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
Title: Re: Excel 2003: use number from text
Post by: Salmon Trout on April 24, 2010, 02:06:17 PM: 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.
Title: Re: Excel 2003: use number from text
Post by: soybean on April 26, 2010, 09:45:05 AM: Another option for the formula: =A1+B1+C1+RIGHT(D1,1)
Title: Re: Excel 2003: use number from text
Post by: Salmon Trout on April 26, 2010, 11:32:53 AM: Quote from: soybean on April 26, 2010, 09:45:05 AM
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.
Title: Re: Excel 2003: use number from text
Post by: soybean on April 26, 2010, 12:02:59 PM: 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.
Title: Re: Excel 2003: use number from text
Post by: Two-eyes on April 26, 2010, 01:21:29 PM: 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 %
Title: Re: Excel 2003: use number from text
Post by: Salmon Trout on April 26, 2010, 01:24:00 PM: Quote from: Two-eyes on April 26, 2010, 01:21:29 PM
I think soybean's is the better approach

:'(
Title: Re: Excel 2003: use number from text
Post by: soybean on April 26, 2010, 01:51:38 PM: Quote
I think soybean's is the better approach

;D