How can you replace specific characters in a string ?

Date Product Qty Inventory
1/1/2010 Apples 1 Apples(1)
1/2/2010 Oranges 2 Apples(1),Oranges(2)
1/3/2010 Apples 7 Apples(7),Oranges(2)
1/4/2010 Apples 0 Oranges(2)


On 1/1/2010, the inventory consisted of 1 apple.

On 1/2/2010, the inventory consisted of 1 apple and 2 oranges.

On 1/3/2010, the inventory consisted of 7 apples and 2 oranges.

On 1/4/2010, the inventory consisted of 2 oranges.

I have a formula that determines which items are in the Inventory
column. For example, on 1/2/2010, the Inventory column reflects
Apples, Oranges.

The next step is to append to each item its quantity. For example, on
1/2/2010, the Inventory column should reflect Apples(1),Oranges(2).

However, I haven’t been able to figure out how to replace Apples(1)
with Apples(7) on 1/3/2010 or how to remove Apples(7) on 1/4/2010 from
the Inventory column.


Exceluser

On Aug 27, 9:01*pm, exceluser <ifmcqy7aias...@yahoo.com> wrote:
> How can you replace specific characters in a string ?
>
> Date * *Product Qty * * Inventory
> 1/1/2010 * * * *Apples *1 * * * Apples(1)
> 1/2/2010 * * * *Oranges 2 * * * Apples(1),Oranges(2)
> 1/3/2010 * * * *Apples *7 * * * Apples(7),Oranges(2)
> 1/4/2010 * * * *Apples *0 * * * Oranges(2)
>
> On 1/1/2010, the inventory consisted of 1 apple.
>
> On 1/2/2010, the inventory consisted of 1 apple and 2 oranges.
>
> On 1/3/2010, the inventory consisted of 7 apples and 2 oranges.
>
> On 1/4/2010, the inventory consisted of 2 oranges.
>
> I have a formula that determines which items are in the Inventory
> column. For example, on 1/2/2010, the Inventory column reflects
> Apples, Oranges.
>
> The next step is to append to each item its quantity. For example, on
> 1/2/2010, the Inventory column should reflect Apples(1),Oranges(2).
>
> However, I haven’t been able to figure out how to replace Apples(1)
> with Apples(7) on 1/3/2010 or how to remove Apples(7) on 1/4/2010 from
> the Inventory column.
>
> Exceluser

I figured it out.

The solution involved using SEARCH, REPLACE and SUBSTITUTE.



Exceluser

On Aug 28, 3:17*am, exceluser <ifmcqy7aias...@yahoo.com> wrote:
> On Aug 27, 9:01*pm, exceluser <ifmcqy7aias...@yahoo.com> wrote:
>
>
>
>
>
> > How can you replace specific characters in a string ?
>
> > Date * *Product Qty * * Inventory
> > 1/1/2010 * * * *Apples *1 * * * Apples(1)
> > 1/2/2010 * * * *Oranges 2 * * * Apples(1),Oranges(2)
> > 1/3/2010 * * * *Apples *7 * * * Apples(7),Oranges(2)
> > 1/4/2010 * * * *Apples *0 * * * Oranges(2)
>
> > On 1/1/2010, the inventory consisted of 1 apple.
>
> > On 1/2/2010, the inventory consisted of 1 apple and 2 oranges.
>
> > On 1/3/2010, the inventory consisted of 7 apples and 2 oranges.
>
> > On 1/4/2010, the inventory consisted of 2 oranges.
>
> > I have a formula that determines which items are in the Inventory
> > column. For example, on 1/2/2010, the Inventory column reflects
> > Apples, Oranges.
>
> > The next step is to append to each item its quantity. For example, on
> > 1/2/2010, the Inventory column should reflect Apples(1),Oranges(2).
>
> > However, I haven’t been able to figure out how to replace Apples(1)
> > with Apples(7) on 1/3/2010 or how to remove Apples(7) on 1/4/2010 from
> > the Inventory column.
>
> > Exceluser
>
> I figured it out.
>
> The solution involved using SEARCH, REPLACE and SUBSTITUTE.
>
> Exceluser- Hide quoted text -
>
> - Show quoted text -

For archival purposes post YOUR solution.