Extracting a Part of a Formula as Text

  • Thread starter Thread starter cardan
  • Start date Start date
C

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.
 
=RIGHT(A1,2) entered in Sheet2 A2

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

=RIGHT(Sheet1!B2,2)


Gord
 
=RIGHT(A1,2) entered in Sheet2 A2

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

=RIGHT(Sheet1!B2,2)


Gord
Click to expand...

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
Click to expand...
 
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.
Click to expand...

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


Regards
Claus Busch
 
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
 
Back
Top