Replace numbers in Col B with numbers from Col A

Posted by Victor on September 27, 2001 10:39 AM

I have thousands of numbers in Column A of an excel spreadsheet that I don't want to have to input one by one in between the text and brackets in Column B. I basically want to move a number from one column to replace the number located in between the text and brackets in another column that is in the same row.

My excel example:

Row Column A contains: Column B contains:
1 345 Help(132)Me
2 5786 Help(64)Me
3 9845 Help(898)Me
4 84 Help(784)Me
5 75 Help(90)Me

I want to move what's in column A to replace the numbers that are in the middle of the text in column B. Note:All text is the same in every row! So in other words, when it's done, I would like Column B to look like this:

Row Column A contains: Column B contains:
1 345 Help(345)Me
2 5786 Help(5786)Me
3 9845 Help(9845)Me
4 84 Help(84)Me
5 75 Help(75)Me

Please help! Any info would be greatly appreciated :)

Victor

Posted by IML on September 27, 2001 10:44 AM

Try the formula
=REPLACE(B4,FIND("(",B4),FIND(")",B4)-FIND("(",B4),"("&A4)

you can copy and paste special values over and delete the first to columns, if you wish.

Posted by IML on September 27, 2001 10:46 AM

Pasted wrong

If you are starting in row 1, use
=REPLACE(B1,FIND("(",B1),FIND(")",B1)-FIND("(",B1),"("&A1)
and copy down as far as you need to.

Posted by Barrie Davidson on September 27, 2001 10:46 AM

In column C put this formula:
=MID(B1,1,FIND("(",B1))&A1&RIGHT(B1,LEN(B1)-FIND(")",B1)+1)

then copy and paste values in column B.

Regards,
BarrieBarrie Davidson