Left or right function

[Burnleyfc]

Is there a way to get the left or right function to take information from a formula within a cell as opposed to the value returned in a specific cell.

 

[dk]

Hi,

I don't think there's a way of doing this using normal formulas. You can however use two very simple UDFs (User Defined Functions). Try these two. You need to open the Visual Basic Editor (Alt F11), then choose Insert, Module. Then paste the code from below:-

Function LeftFormula(CellRef As Range, Num_Chars)
LeftFormula = Left$(CellRef.Formula, Num_Chars)
End Function

Function RightFormula(CellRef As Range, Num_Chars)
RightFormula = Right$(CellRef.Formula, Num_Chars)
End Function

You'd then use these like so:-

Summary auto.xls
	A	B	C	D	E
1	ExampleofusingRightFormulaandLeftFormulaUDFs
2
3		380	167	769	256
4		696	836	902	483
5		109	803	16	736
6		108	594	626	398
7		129	519	56	511
8		893	766	681	70
9		699	112	578	364
10		797	557	105	626
11	Normalformulas	3811	8	902	70
12	UsingLeftFormula	=SUM(B3:B10)	=COUNT(C3:C10)	=MAX(D3:D10)	=MIN(E3:E10)
13	UsingRightFormula	(B11,255)	(C11,255)	(D11,255)	(E11,255)
Sheet1

This message was edited by dk on 2002-11-06 05:10

 

[Burnleyfc]

Thanks for your help Dan - that's perfect.

David