Extracting a Part of a Formula as Text

[cardan]

Hello, I have what I think may be an easy question. I am trying to extract part of the formula into a new cell.

In Cell A1 in Sheet2, the formula is =Sheet1!B2. In Cell A2,Sheet2, I need a formula that will return the last 2 characters in the formula "B2".

Is this possible without VBA? Any help would be greatly appreciated. Thank You.

 

[Gord Dibben]

=RIGHT(A1,2) entered in Sheet2 A2

Or if you want, dispense with A1 formula and enter just

=RIGHT(Sheet1!B2,2)


Gord

 

[cardan]

=RIGHT(A1,2) entered in Sheet2 A2

Or if you want, dispense with A1 formula and enter just

=RIGHT(Sheet1!B2,2)


Gord

Hi Gord, Thank you for the reply. Your formula returns the value in the cell. I am looking for it to return the last 2 characters in the formula. RIGHT, LEFT, MID, FIND, deal with texts within the cell value(output). I need it to look at the "text" of the formula.

=RIGHT(A1,2) entered in Sheet2 A2

Or if you want, dispense with A1 formula and enter just

=RIGHT(Sheet1!B2,2)


Gord

 

[Claus Busch]

Hi,

Am Wed, 6 Jun 2012 13:33:18 -0700 (PDT) schrieb cardan:

Your formula returns the value in the cell. I am looking for it to return the last 2 characters in the formula. RIGHT, LEFT, MID, FIND, deal with texts within the cell value(output). I need it to look at the "text" of the formula.

you have to do it with VBA:
With Sheets("sheet2")
.Range("A2") = Right(.Range("A1").Formula, 2)
End With


Regards
Claus Busch

 

[Gord Dibben]

You're right.........I missed your request.

See Claus's reply.

Gord

 

[Bob Flanagan]

One way to do without VBA is to first do an edit, replace, and replace
all equal signs in your range with "zz=". This makes the formulas
text, then you can use the Right() function to return characters.
=Right(a1,2). Then copy, paste special the Right() formulas to make
them values. Lastly, Change all "zz=" back to just "=". The search
and replace needs to look at formulas vs values.

VBA is easier )

Robert Flanagan
Add-ins.com LLC
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel