Left or right function

Burnleyfc

New Member
Joined
Aug 29, 2002
Messages
10
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.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

dk

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
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:-

Code:
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
ABCDE
1ExampleofusingRightFormulaandLeftFormulaUDFs
2
3380167769256
4696836902483
510980316736
6108594626398
712951956511
889376668170
9699112578364
10797557105626
11Normalformulas3811890270
12UsingLeftFormula=SUM(B3:B10)=COUNT(C3:C10)=MAX(D3:D10)=MIN(E3:E10)
13UsingRightFormula(B11,255)(C11,255)(D11,255)(E11,255)
Sheet1

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

Forum statistics

Threads
1,144,221
Messages
5,723,115
Members
422,478
Latest member
Rovan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top