 # 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

• 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;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

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;703776

Ok 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;703985

Yes, 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.

## Files

• Book2.xlsx

• 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

• 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)