Dissecting a Formula

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have a formula "=A00011220" in Cell A1; however, the result of A1 is "A".

In Cells C1, D1 and E1, based on the formula in A1, I want them to show
"A0001", "12" and "20", respectively. The formula in A1 will be the same
format anytime.

Basically, is there a function in Excel that remove the "=" from a formula
and turn it into text or a function that turns the actual formula into text?

Thanks.
 
When I entered =A00011220 into cell A1 Excel truncated it to read
=A11220 (i.e., cell #11220 in column A as though it was a typical cell
reference). Do you have any apostrophes in your entry?
 
The following small UDF will return the formula as string without the = sign:

Function form2text(r As Range) As String
v = r.Formula
form2text = Right(v, Len(v) - 1)
End Function
 
Yes, it is a defined name. I should have mentioned that in my original
message. Sorry!

Is there a way to do it? Thanks.
 
Hi,

Try with find and replace

find: =
and leav replace blank
after doing that enter formula below for C1, D1 and E1:

C1: =LEFT(A1,5)
D1: =MID(A1,6,2)
E1: =RIGHT(A1,2)

Thanks,
 
Gary,

You are genius. How did you know to do that? Can you give me some tips on
where I can go to learn those codes?

Thanks.
 
I am not a genius, only a student. Get a good book on Excel VBA, and in a
couple of weeks you will probably know more than me.
 
Back
Top