Hard Numbers in a formula

[Matt_Chelmsford]

Hi,

I have values in cells A1 to A100

A value may look like =1234-4561+456-123

or =456+987

Or even 1234

My aim here is to give each individual value it's own cell

So assume my first example is in A1 then my aim is for B1 = 1234 C1 -4561, etc.

Any help given would be gratefully appreciated.

Regard

Matthew

 

[James006]

Hello,

In your Column A ... are you dealing with Text or actual Formulas for which the result is already displayed ...?

Cheers

 

[Ron Coderre]

Assuming cell A1 contains a formula that resolves to -2994
This regular formula, copied across to the right, returns the component positive and negative values:

B1: 
=TRIM(
    MID(
        SUBSTITUTE(
            SUBSTITUTE(
                SUBSTITUTE(
                    SUBSTITUTE(FORMULATEXT($A1),"+","|+")
                ,"-","|-")
            ,"=","")
        ,"|",REPT(" ",100))
    ,(COLUMNS($B:B)-1)*100+1
    ,100)
 )

These are the results:

Col_B    Col_C    Col_D    Col_E
1234     -4561    +456     -123

Is that something you can work with?

 

[James006]

@Ron

Love your very funny trick ...!!!