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.

I don't understand =A00011220 as a formula. Is A00011220 a defined name?
--
David Biddulph

"AccessHelp" <(E-Mail Removed)> wrote in message
news:F8594B84-F569-426A-9224-(E-Mail Removed)...
> 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

--
Gary''s Student - gsnu200746


"AccessHelp" wrote:

> 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.

Yes, it is a defined name. I should have mentioned that in my original
message. Sorry!

Is there a way to do it? Thanks.

"David Biddulph" wrote:

> I don't understand =A00011220 as a formula. Is A00011220 a defined name?
> --
> David Biddulph
>
> "AccessHelp" <(E-Mail Removed)> wrote in message
> news:F8594B84-F569-426A-9224-(E-Mail Removed)...
> > 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.
>
>
>

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,
--
Farhad Hodjat


"AccessHelp" wrote:

> 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.

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.


"Gary''s Student" wrote:

> 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
>
> --
> Gary''s Student - gsnu200746
>
>
> "AccessHelp" wrote:
>
> > 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.

Thanks guys for your help.

"Dave O" wrote:

> 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?
>
>

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.
--
Gary''s Student - gsnu200746


"AccessHelp" wrote:

> 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.
>
>
> "Gary''s Student" wrote:
>
> > 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
> >
> > --
> > Gary''s Student - gsnu200746
> >
> >
> > "AccessHelp" wrote:
> >
> > > 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.

Would you recommend any Excel VBA book?

Thanks.



"Gary''s Student" wrote:

> 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.
> --
> Gary''s Student - gsnu200746
>
>
> "AccessHelp" wrote:
>
> > 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.
> >
> >
> > "Gary''s Student" wrote:
> >
> > > 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
> > >
> > > --
> > > Gary''s Student - gsnu200746
> > >
> > >
> > > "AccessHelp" wrote:
> > >
> > > > 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.