Seperating digits from a number [excel formulas]

My state tax dept requires a “code” for payment amounts which is the sum of the digits for the payment plus the number of digits.
Example: $1045.99 = code 34
1 + 0 + 4 + 5 + 9 + 9 plus 6 (digits in my pymt amt)

Would the floor() function do that?

Also, I would never have known what the floor() function would do. Is there some type of function definition list somewhere?

@Joann Lindenschmidt
Hello Joann. I’m a new reader on this great site. Maybe I can help you. The Floor function rounds a number down to the nearest multiple of significance you specify. For example if you take 2.5 and specify 1 as the multiple the result would be 2. In the example above the Floor function eliminates noise in the formula. One way to see functions and to get help with them is by clicking on the fx (insert function) button on the left of the formula bar. You can type in a function or select all and click through each function to see what they do. You can also let the insert function dialogue box walk you through setting up a function. Its very useful when you are new to functions.

The Left function might also be used to seperate digits from a number. In the example above Left(117490,4) would return 1174.

I don’t think the Floor function would help you in the example you posted but I may be missing something.

Here’s a challenge I got from work: a colleague needs to visually extract a number from a longer string, and wanted a way that was less eye-straining. The number is less than 10.0 (ie., only one digit in front of the decimal), and may have between one and four digits after the decimal. There is a space before and a space after, but all sorts of things can come in the string as well. How can I reliably extract that number from the junk?

My solution, IIRC, was

=MID(A1&REPT(” “,12),(SEARCH(A1,”?.????”))-3,12)

and I conditionally formatted to de-emphasise the #VALUE! that shows up when the number is absent or undetected. Including the three characters on either side of the number helps in visually confirming that the detection was accurate, and the twelve appended spaces avoid errors caused by the number being too close to the end of the original string.

Any improvements?

Rats. Make that ” ?.????”. I include the leading space for the detection. as it is the only reliable element, besides the decimal point. If only Excel had regexp so I could instruct it to look for digits!

Derek,

here is a more general, array-based approach (Text is in cell A1):

=MID(LEFT(A1,MAX(IF(ISNUMBER(MID(A1,COLUMN(1:1),1)*1),COLUMN(1:1)))),MATCH(TRUE,ISNUMBER(MID(A1,COLUMNS(1:1),1)*1),0),LEN(A1))

Advantages:

1. Handles all options (number at the beginning, at the end, in the middle)
2. Works for all numbers regardless the number of digits
3. Works for dates and times as well

Shortfalls:

1. It is an array formula and we all know about the advantages and disadvantages of array formulas.

2. Returns an error if the text does not include a number. IF (ISERROR(formula),””,formula) can only be used in Excel 2007. Excel 2003 and earlier has a limitation of nested IFs (I think maximum 7), therefore you have to use user-defined formats or an extra help column for the error handling.

3. It does not handle negative values (‘-‘ is treated as a character and therefore cut off). The formula always brings back the positive value.

4. It does not work properly for texts with more than one number. It will bring back a string with everything between the first digit of the first number and the last digit of the last number.

5. You might run into a problem with the different numbering formats in different countries. E.g. in the U.S. you are using ‘,’ as the thousand separator and ‘.’ as the decimal point. In Europe it is exactly the other way round.

If you are interested, here is a download link for the file:

http://www.box.net/shared/bgpad33mhe

O.k, this formula is obviously too long for a Wordpress comment. Here it is again:

=MID(

LEFT(A1,MAX(IF(ISNUMBER(MID(A1,COLUMN(1:1),1)*1),COLUMN(1:1)))),

MATCH(TRUE,ISNUMBER(MID(A1,COLUMNS(1:1),1)*1),0),

LEN(A1))

As said above an array formula, insert it with ctrl-shift-return.

@Joann ,Chandoo :

Cud u pls make cusotmise function in VBA by using the following in loop :
1)function “len”
2)function “mid”
This function can be used in any cell.

Logic :
Say the name of function PHDC ( )
Use counter and make a loop for “No. of Characters” (by “len” function)
Use “mid” function and store the value of each loop
Close the loop till counter reach to “No. of Characters”
The final formula in result cell will be =PHDC( ) + len ( )
Just make and inform

I liked Robert’s suggestion, and I’ll try it out later this week if I get time.

hi, please somebody help me to solve my problem as bellow:
i have a double digit in single cell = 12
i need the sum of that digit =3
is it possible, if yes plz reply me to my email.(pradeepkiggal@gmail.com)