How can you replace specific characters in a string ?

[exceluser]

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

 

[exceluser]

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

 

[Don Guillett Excel MVP]

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.