Forum Discussion

amol bhosale's avatar
amol bhosale
Copper Contributor
Dec 17, 2017

Separate in 2 cell


Cell A2:
RED LABEL TEA CARTO N 250G 90% 82.57 80.89 9.0% 11.3%

After use formula when above line 90%
Cell B2:
RED LABEL TEA CARTO N 250G

Enter below formula in cell B2:
=IF(ISNUMBER(FIND("%",A2)),LEFT(A2,LOOKUP(1E+100,FIND(" ",LEFT(A2,FIND("%",A2)-1),ROW($1:$1016)))-1),LEFT(A2,LOOKUP(1E+100,FIND(" ",A2,ROW($1:$1016)))-1))

Cell C2:
90% 82.57 80.89 9.0% 11.3%

Enter below formula in cell C2:
=IF(ISNUMBER(FIND("%",A2)),RIGHT(A2,LEN(A2)-LOOKUP(1E+100,FIND(" ",LEFT(A2,FIND("%",A2)-1),ROW($1:$1016)))),RIGHT(A2,LEN(A2)-LOOKUP(1E+100,FIND(" ",A2,ROW($1:$1016)))))

We use above mentioned formula when % in number(90)

But i have without % number in below line. So please help me how to change the above formula.

RED LABEL TEA CARTO N 250G 90 82.57 80.89 9.0% 11.3%

Thanks.
excel
Formulas and Functions
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Dec 17, 2017

    Hi amol,

     

    Current logic is:

    - find first "%" in the string;

    - put in one cell the part of the string to the left from first space before "%";

    - put in another cell the part of the string to the right of such space.

     

    Your modified logic in unclear.  Would you like to separate on first number without any letters? For example

    RED LABEL TEA RS.5/- 14G PK24 120 91.00 89.82 31.9% 33.6%

    shall be split on

    RED LABEL TEA RS.5/- 14G PK24
120 91.00 89.82 31.9% 33.6%

    or what?

     

    In any case, I guess it'll be another set of formulas for another logic.

Resources