IF FORMULA that divide 1 column to 2

  • hello to all.


    can some one please help me with this , i need to tack column G and put the data in L and M.


    for example i got in G1 this data: .53x.56


    so i need to move .53 to L1 and .56 to M1 and get rid of the x


    i'm sending the file.


    thanks.


    avi.

    Files

    • Book3.rar

      (435 Byte, downloaded 90 times, last: )
  • Re: IF FORMULA that divide 1 column to 2


    Hi,


    No need formula, try Text To Column.


    Date --> Text To Column -->Next --> Other (here put "x" Without "") and from Destination, choose L1 --> Finish

  • Re: IF FORMULA that divide 1 column to 2


    Quote from patel;703771

    attach please a xls file (no csv) with data and desired result


    thank you patel , i don't see the file can you please check?

  • Re: IF FORMULA that divide 1 column to 2


    Quote from Ingo_Ingo;703773

    Hi,


    No need formula, try Text To Column


    thanks, i know but i need formula to save time.

  • Re: IF FORMULA that divide 1 column to 2


    can i add to this formula =VALUE(MID(C1,(FIND(" ",C1,6)+1),FIND(" ",C1,FIND(" ",C1,6)+1)-(FIND(" ",C1,6)+1)))


    THIS ONE


    =IF(ISNUMBER(C1) TO CONTINUE THE ABOVE FORMULA AND IF NOT PUT A ZERO?

  • Re: IF FORMULA that divide 1 column to 2


    If this formula is ok
    =VALUE(MID(C1,(FIND(" ",C1,6)+1),FIND(" ",C1,FIND(" ",C1,6)+1)-(FIND(" ",C1,6)+1)))


    Try this:


    =IF(ISNUMBER(C1),VALUE(MID(C1,(FIND(" ",C1,6)+1),FIND(" ",C1,FIND(" ",C1,6)+1)-(FIND(" ",C1,6)+1))),0)

  • Re: IF FORMULA that divide 1 column to 2


    This making all the cells to zero
    in column c i got this kind of data :
    mar 14 26 p (with this data the value formula give the number 26 this what i need)
    or
    3/14/2014 12:00:00 pm (with this data i got #value! And this is bad for me i need it to be zero)


    just to be clear about it in column c i have some times this data mar 14 26 p and some times 3/14/2014 12:00:00 pm my problem is with this 3/14/2014 12:00:00 pm , when this happen i want the number zero.

  • Re: IF FORMULA that divide 1 column to 2


    In general you can not add apples to oranges, ie to add numbers with letters (some of the numbers were seen as text). Errors are more, but I rebuilt formulas in the file. Look at the formulas and study them so that you can understand in the future.
    See the attached file.

    Files

    • Book2.xlsx

      (19.16 kB, downloaded 75 times, last: )
  • Re: IF FORMULA that divide 1 column to 2


    400396,


    You must be more explicit. Where? what column? what formula to which you refer? I can not guess. My crystal ball tells me that you want to change the formula in column N. If so put this formula in N1
    =IF(RIGHT(C1;1)="P";2;IF(RIGHT(C1;1)="C";1;0))
    and "drag" down to where it's needed.

  • Re: IF FORMULA that divide 1 column to 2


    sorry about that , i'm talking about the formula that you made on colomn O, IFERROR(VALUE(MID(C1,(FIND(" ",C1,6)+1),FIND(" ",C1,FIND(" ",C1,6)+1)-(FIND(" ",C1,6)+1))),0)


    i want to add to that if C is blank to get zero.