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.
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.
Re: IF FORMULA that divide 1 column to 2
attach please a xls file (no csv) with data and desired result
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;703771attach 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;703773Hi,
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
Ok then,
L1 =LEFT(G1,FIND("x",G1)-1)
M1=RIGHT(G1,LEN(G1)-FIND("x",G1))
Re: IF FORMULA that divide 1 column to 2
Quote from Ingo_Ingo;703776Ok then,
L1 =LEFT(G1,FIND("x",G1)-1)
M1=RIGHT(G1,FIND("x",G1)-1)
thank you.
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
Yes, you can. Did you tried and is not working?
Re: IF FORMULA that divide 1 column to 2
Quote from Ingo_Ingo;703985Yes, you can. Did you tried and is not working?
i'm not sure how i can do it.
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
Upload a file (obviously no confidential data) to see what type of data you have.
Re: IF FORMULA that divide 1 column to 2
There is the file, thank you.
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.
Re: IF FORMULA that divide 1 column to 2
Thank you ingo , i appriciate your help.
Re: IF FORMULA that divide 1 column to 2
ingo can you please add to the formula if the cell is blank (with out any data ) to get zero?
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.
Re: IF FORMULA that divide 1 column to 2
In O1 =IFERROR(IF(C1="";0;VALUE(MID(C4;(FIND(" ";C4;6)+1);FIND(" ";C4;FIND(" ";C4;6)+1)-(FIND(" ";C4;6)+1))));0)