Function to split formula contents of a cell to columns

[acurious1982]

Hi,

OK, imagine this simple scenario:

Cell A1 says:
=5*4

now in cell A2 I want to only be able to extraact the charecter "5"

I know how to use Text to Split feature, but I dont neither wanna use that feature nor any VBA programing please!

I know how to use search, mid, left, right, etc and all these functions but not if I wanna extract a part of a cell which has a formula, that simply doesnt work!!!

I appreaciate any help

thanks very much in advance

 

[Teeroy]

I don't think it is possible to do this with a built in function. The function acts on the cells value property, not its formula property, so it won't even see how the value is created.

You could do this using a User Defined Function (UDF), but you have to write the UDF in VBA. A simple UDF to do what you proposed is (in a standard VBA code module):

Function get_num(rng As Range)
get_num = Evaluate(Split(rng.Formula, "*")(0))
End Function

Used as
A2: =get_num(A1)

 

[XOR LX]

Alternatively, assuming your formula is in A1, go to Name Manager and create a new name, say Get_Formula,and enter this in the Refers to: box:

=GET.CELL(6,Sheet1!A1)

Exit Name Manager. Then, in B1, enter this formula:

=MID(Get_Formula,FIND("=",Get_Formula)+1,1)

Obviously you can amend the MID construction to something a bit more dynamic (e.g. if the desired extraction is not always of length one), but since you said you have experience in using these sorts of formulas I will leave that to you.

Regards