Find and Replace Formula

[Ruan]

Hello,

I need some help with a complex find and replace formula. I need to be able
to find predefined markers (**1**, **2** etc) within a paragraph and replace
them with calculated values from cells A1, A2 and A3 respectively.

Example of paragraph
The calculation of **1** and **2** equates to **3**.

Example of result
The calculation of 748 and 27 equates to 20196.

Thanks
Ruan

 

[T. Valko]

Try this:

A10 = The calculation of **1** and **2** equates to **3**.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A10,"**1**",A1),"**2**",A2),"**3**",A3)

Biff

 

[Ruan]

Thanks, however I have a cell that contains a paragraph of text that has
approx 260 characters. example -

Inforce policy count for New Policies (Term 0) and Renewed Policies (Term 1
and greater) grow at a rate of **1** and **2** respectively, resulting in a
**3** combined growth of inforce policies. New Policies show a more
volatile growth pattern over the year due to seasonality whereas the growth
in Renewed policies is a reflection of a relatively constant
renewal/cancellation rate after the first year.

I am using the current formula which only replaces **1** and **2**
=REPLACE(REPLACE(A5,FIND("**1**",A5),5,A1),FIND("**2**",REPLACE(A5,FIND("**1**",A5),5,A1)),11,A2)

Ruan

 

[RagDyeR]

Why not just continue with your formula to replace the **3** with A3, and
just enter the calculation in A3 as:
=A1*A2

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Thanks, however I have a cell that contains a paragraph of text that has
approx 260 characters. example -

Inforce policy count for New Policies (Term 0) and Renewed Policies (Term 1
and greater) grow at a rate of **1** and **2** respectively, resulting in a
**3** combined growth of inforce policies. New Policies show a more
volatile growth pattern over the year due to seasonality whereas the growth
in Renewed policies is a reflection of a relatively constant
renewal/cancellation rate after the first year.

I am using the current formula which only replaces **1** and **2**
=REPLACE(REPLACE(A5,FIND("**1**",A5),5,A1),FIND("**2**",REPLACE(A5,FIND("**1**",A5),5,A1)),11,A2)

Ruan

 

[T. Valko]

Thanks, however I have a cell that contains a paragraph of text that has

approx 260 characters. example -

Actually, your paragragh contains 404 chars.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A10,"**1**",A1),"**2**",A2),"**3**",A3)

Works ok for me.

Inforce policy count for New Policies (Term 0) and Renewed Policies (Term
1and greater) grow at a rate of 748 and 27 respectively, resulting in a
20196 combined growth of inforce policies. New Policies show a more
volatile growth pattern over the year due to seasonality whereas the growth
in Renewed policies is a reflection of a relatively constant
renewal/cancellation rate after the first year.

Biff

 

[Ruan]

Awesome....thanks Biff, that works great. I can't believe how easy that was.

Ru

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

Awesome....thanks Biff, that works great. I can't believe how easy that
was.

Ru