excel-formula

If Cell Starts with Text String… Formula

by

I’m not sure lookup is the right formula for this because of multiple arguments. Maybe hlookup or vlookup but these require you to have tables for values. A simple nested series of if does the trick for a small sample size Try =IF(A1=”a”,”pickup”,IF(A1=”b”,”collect”,IF(A1=”c”,”prepaid”,””))) Now incorporate your left argument =IF(LEFT(A1,1)=”a”,”pickup”,IF(LEFT(A1,1)=”b”,”collect”,IF(LEFT(A1,1)=”c”,”prepaid”,””))) Also note your usage of left, … Read more

Excel formula to remove space between words in a cell

by

Suppose the data is in the B column, write in the C column the formula: =SUBSTITUTE(B1,” “,””) Copy&Paste the formula in the whole C column. edit: using commas or semicolons as parameters separator depends on your regional settings (I have to use the semicolons). This is weird I think. Thanks to @tocallaghan and @pablete for … Read more

Extract the last substring from a cell

by

This works, even when there are middle names: =MID(A2,FIND(CHAR(1),SUBSTITUTE(A2,” “,CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,” “,””))))+1,LEN(A2)) If you want everything BUT the last name, check out this answer. If there are trailing spaces in your names, then you may want to remove them by replacing all instances of A2 by TRIM(A2) in the above formula. Note that it is only … Read more

Decimal to binary conversion for large numbers in Excel

by

If we are talking positive number between 0 and 2^32-1 you can use this formula: =DEC2BIN(MOD(QUOTIENT($A$1,256^3),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^2),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^1),256),8)&DEC2BIN(MOD(QUOTIENT($A$1,256^0),256),8) NOTE: =DEC2BIN() function cannot handle numbers larger than 511 so as you see my formula breaks your number into four 8-bit chunks, converts them to binary format and then concatenates the results. Well, theoretically you can extend this formula … Read more