Can't Find an Answer to this Question Anywhere! Please help me MR. Excel Geniuses!!!

[maren_loves_excel]

So here's what I'm trying to do:

I have a product description in Column B and within that there is some text in a (), I want to replace the text within the () with another number/text combo from Column A

Here's an example

<tbody>
</tbody>

ARR112

<p>The 14-3/4" x 10-1/2" Oval Platter (ARR112-02) by Carlisle provides any restaurant…..

<tbody>
</tbody>

Does any one out there know of a way to do this? Please make it easy if you can, I'm a noob.

 

[Michael M]

HAve you considered using Find / replace ?
In the find box type (*)
In the replace box type (your number)

 

[thisoldman]

I think you mean you have many rows where you would like to perform replacements? This is one way to do it that I think I can explain.

Column A has the new replacement text, column B has the text you wish to change. We'll assume the first of these two cells are A1 (substitution text) and B1 (original text).

The left hand part
The position of the left parenthesis can be found with: =FIND("(", B1)
We want all the text up to and including the "(" to remain as it is in the original: =LEFT(B1, FIND("(", B1)
Then we add your substitution text from cell A1: =LEFT(B1, FIND("(", B1) & A1

The right hand part
We've got everything as we want, including the substitution. Now we want the right hand portion of the original text from the ")" onward.

We need to calculate the length of that right hand text so we can tell Excel how many characters from the original text to include.

The length of the original text is: =LEN(B1)
The position of the right parenthesis can be found with: =FIND(")", B1)
We calculate how many characters come after the ")" by using: =LEN(B1) - FIND(")", B1)
But that does not include the ")" itself, so we add one to that last calculation: =LEN(B1) - FIND(")", B1) + 1
We combine this last formula with RIGHT to come up with the string we will need to add to what we already have for the left hand part:
=RIGHT(B1, LEN(B1) - FIND(")", B1) + 1)

Combining the left hand with the right hand parts
=LEFT(B1, FIND("(", B1)) & A1 & RIGHT(B1, LEN(B1) - FIND(")", B1) + 1)

 

[maren_loves_excel]

I think you mean you have many rows where you would like to perform replacements? This is one way to do it that I think I can explain.

Column A has the new replacement text, column B has the text you wish to change. We'll assume the first of these two cells are A1 (substitution text) and B1 (original text).

The left hand part
The position of the left parenthesis can be found with: =FIND("(", B1)
We want all the text up to and including the "(" to remain as it is in the original: =LEFT(B1, FIND("(", B1)
Then we add your substitution text from cell A1: =LEFT(B1, FIND("(", B1) & A1

The right hand part
We've got everything as we want, including the substitution. Now we want the right hand portion of the original text from the ")" onward.

We need to calculate the length of that right hand text so we can tell Excel how many characters from the original text to include.

The length of the original text is: =LEN(B1)
The position of the right parenthesis can be found with: =FIND(")", B1)
We calculate how many characters come after the ")" by using: =LEN(B1) - FIND(")", B1)
But that does not include the ")" itself, so we add one to that last calculation: =LEN(B1) - FIND(")", B1) + 1
We combine this last formula with RIGHT to come up with the string we will need to add to what we already have for the left hand part:
=RIGHT(B1, LEN(B1) - FIND(")", B1) + 1)

Combining the left hand with the right hand parts
=LEFT(B1, FIND("(", B1)) & A1 & RIGHT(B1, LEN(B1) - FIND(")", B1) + 1)

You are so awesome, this worked perfectly!!

 

[rakeshshethia]

You are so awesome, this worked perfectly!!

how do i get rid of parenthesis in the end result?

 

[rakeshshethia]

I think you mean you have many rows where you would like to perform replacements? This is one way to do it that I think I can explain.

Column A has the new replacement text, column B has the text you wish to change. We'll assume the first of these two cells are A1 (substitution text) and B1 (original text).

The left hand part
The position of the left parenthesis can be found with: =FIND("(", B1)
We want all the text up to and including the "(" to remain as it is in the original: =LEFT(B1, FIND("(", B1)
Then we add your substitution text from cell A1: =LEFT(B1, FIND("(", B1) & A1

The right hand part
We've got everything as we want, including the substitution. Now we want the right hand portion of the original text from the ")" onward.

We need to calculate the length of that right hand text so we can tell Excel how many characters from the original text to include.

The length of the original text is: =LEN(B1)
The position of the right parenthesis can be found with: =FIND(")", B1)
We calculate how many characters come after the ")" by using: =LEN(B1) - FIND(")", B1)
But that does not include the ")" itself, so we add one to that last calculation: =LEN(B1) - FIND(")", B1) + 1
We combine this last formula with RIGHT to come up with the string we will need to add to what we already have for the left hand part:
=RIGHT(B1, LEN(B1) - FIND(")", B1) + 1)

Combining the left hand with the right hand parts
=LEFT(B1, FIND("(", B1)) & A1 & RIGHT(B1, LEN(B1) - FIND(")", B1) + 1)

how do i get rid of parenthesis in the end result

 

[thisoldman]

=left(b1, find("(", b1) - 1) & a1 & right(b1, len(b1) - find(")", b1))

 

[rakeshshethia]

=left(b1, find("(", b1) - 1) & a1 & right(b1, len(b1) - find(")", b1))

Perfect! Thank you.