Text to Value Advance

Kalau ada cell yang didalamnya terdapat text (biasanya dari kantor pajak) bisa menggunakan formula dibawah ini untuk men convert nya ke dalam value .
Contoh, misalkan di cell A1 ada text dengan koma dan terdapat spasi pada akhir textnya :

'12,500,000 , maka dengan mengunakan formula
= VALUE(T(TRIM(SUBSTITUTE(A1,CHAR(160),"")))) hasilnya menjadi 12500000

IF Function Max 7 Condition

Excel allows up to seven levels of nested IF functions. The formula below works correctly, but Excel will not allow you to nest the IF functions any deeper than this. The alternate you can use the following function :

1. VLOOKUP
In many cases, you can avoid using IF functions and use a VLOOKUP function. This will require a separate table in your worksheet.

2. CONCATENATE function
Another option was suggested by B. Ganesh, use the CONCATENATE function. In this case, each argument for CONCATENATE consists of an IF function. Here's an example:
=CONCATENATE(IF(A1="A",1,""),IF(A1="B",2,""),IF(A1="C",3,""),IF(A1="D",4,""),IF(A1="E",5,""),IF(A1="F",6,""),IF(A1="G",7,"")),F(A1="H",8,""),IF(A1="I",9,""),IF(A1="J",10,""))

3. & Function,
Example : =(IF(A1="A",1,""),IF(A1="B",2,""),IF(A1="C",3,""),IF(A1="D",4,""),IF(A1="E",5,""),IF(A1="F",6,""),IF(A1="G",7,"")),& IF(A1="H",8,""),IF(A1="I",9,""),IF(A1="J",10,""))

SubTotal Function

You can use subtotal function not only 9, but you can use the other code as well from 1-11 as follow :

1. Average, 2. Count, 3. CountA, 4. Max, 5. Min, 6. Product, 7. STDev, 8. STDevP, 9. Sum, 10.Var, 11.VarP

Example = SUBTOTAL(1,A1:A4) --> will calculate the subtotal average from range A1-A4

Please try

Break Formula Link

Cara tercepat untuk membuat value andaikan ada sejumlah formula link dari luar source data adalah menggunakan Break Link yaitu Click Edit, Link lalu pilih source locationnya dan clik BREAK LINK.

Maximum text in a single cell - Percaya atau Tidak

Maximum text character to display in cell Excel is allowed 32,767 characters in the formula bar but only 1,024 characters display in a cell.

Jumlah text yang dapat ditampilkan di dalam sebuah cell adalah 32,767 hurup akan tetapi yang dapat di display maximum 1,024 hurup saja. Percaya atau tidak hitung sendiri.

Convert Text to Value

Tired with convert text to value, try using the following formula :

= Value(T(A1))

Removed space

If you have a character (value or text) that containing a space in last character i.e DVD1233x (says x is a space that mostly is blank) you can delete that space by using the formula :
1. = TRIM(A1)
2. Text To Column Function
3. = TRIM(SUBSTITUTE(A1,CHAR(160),""))