On Mar 12, 3:21 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
Not sure how efficient it is, but I think this formula does what you asked
for (and can be easily expanded to handle other values)...

=IF(ISNUMBER(MATCH(RIGHT(A1,5),{"10000","11000","1 2000"},0)),LEFT(A1,14)&CHOOSE(MATCH(RIGHT(A1,5),{" 10000","11000","12000"},0),"10234","11003","12003" ),A1)

Rick

"Jeffrey" wrote in message

...

I have a worksheet with one column which contains numerous GL account
codes suchs as:

001-400010-00-11000
001-400010-00-12000
001-500000-00-10000
001-500000-00-10234
001-500000-00-11000
001-500000-00-11003

Basically, I need to make change to the number and output the change
in column b. The numbers that end in "-10000" I need to now end with
10234, those ending with "11000" need to end with 11003, and 12000
need to end with 12003.

I am trying a formula like this:
=IF(SEARCH("-00000",A53),REPLACE(A53,15,5,10234),
IF(SEARCH("-10000",A53),REPLACE(A53,15,5,10234),
IF(SEARCH("-11000",A53),REPLACE(A53,15,5,11003),
IF(SEARCH("-12000",A53),REPLACE(A53,15,5,12003),A53))))

My intent should be clear by the formula above. If it ends with 00000
change that to 10234, ending in 10000 change to 10234 and so on. If
nothing matches then just leave it as is.

As I drag the formula down it works for anything ending with -00000
but nothing else gets anything other than #VALUE!

Any thoughts how I can accomplish this with a formula...

This worked perfectly. Thanks for all the responses. This is the first
time I have got a worthwhile answer on google groups in quite a while.
Thanks again,