Find and Replace Formula
 

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

Find and Replace Formula
 

Try this:

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

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

Biff

"Ruan" wrote in message
...
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

Find and Replace Formula
 

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" wrote in message
...
Try this:

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

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

Biff

"Ruan" wrote in message
...
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

Find and Replace Formula
 

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!
================================================== ===

"Ruan" wrote in message
...
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" wrote in message
...
Try this:

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

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

Biff

"Ruan" wrote in message
...
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

Find and Replace Formula
 

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" wrote in message
...
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" wrote in message
...
Try this:

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

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

Biff

"Ruan" wrote in message
...
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

Find and Replace Formula
 

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

Ru




"T. Valko" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Try this:

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

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

Biff

"Ruan" wrote in message
...
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

Find and Replace Formula
 

You're welcome. Thanks for the feedback!

Biff

"Ruan" wrote in message
...
Awesome....thanks Biff, that works great. I can't believe how easy that
was.

Ru




"T. Valko" wrote in message
...
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" wrote in message
...
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" wrote in message
...
Try this:

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

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

Biff

"Ruan" wrote in message
...
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