Posts by Brandtrock

  • text formula for excel

    Assuming your name list is starts in A1 and that your names are in the form Firstname Lastname, the following formula gives you what you want


    =CONCATENATE(LEFT(A1,1),RIGHT(A1,(LEN(A1)-(SEARCH(" ",A1)))),"@company.com")

  • More problems using the "IF" function

    =IF(OR(C2="UA",F2="UA",I2="UA",L2="UA",O2="UA"),"YES","NO")


    The formula above works if you are going to check for UA in each cell. If the UA is part of another text string you'll need to use the following more complex formula:


    =IF(AND(ISERROR(FIND("UA",C2)),ISERROR(FIND("UA",F2)),ISERROR(FIND("UA",I2)),ISERROR(FIND("UA",L2)),ISERROR(FIND("UA",O2))),"NO","YES")


    FIND is CASE SENSITIVE so take that into account when entering the formula. SEARCH is not CaSe SeNsItIvE so you could use that if you would rather.

  • Can I specify a cell format to divide by 1000

    First, let me apologize for the inaccuracy in my prior post. The Edit tab is where the fixed decimal check box is located.


    Choose fixed decimals to 3 places to divide by 1000 (2 divides by 100 - apparently I was asleep at the keyboard). To display the result with 2 decimal places click the toolbar button to Decrease Decimal once.


    The VBA solution posted by Derk is quite ingenius and much more versatile. By replacing the /1000 in the code you could manipulate the entry by factors other than base 10.

  • Can I specify a cell format to divide by 1000

    Joozh -
    Go to Tools>Options
    Click on the Calculation tab
    Under Fixed Decimal pick 2 places.
    When you type in 123456
    you get 1234.56 in the cell.
    Text will not error out.


    This change will affect your entire workbook, not just a column or range of cells, but it will give the desired outcome. Hope this is what you needed.