Nested Search and Replace

[Jeffrey]

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...

 

[Gary''s Student]

Try this small macro:

Sub change_um()
n = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To n
v = Cells(i, 1).Value
v1 = Left(v, 14)
v2 = Right(v, 5)
If v2 = "10000" Then v2 = "10234"
If v2 = "11000" Then v2 = "11003"
If v2 = "12000" Then v2 = "12003"
Cells(i, 1).Value = v1 & v2
Next
End Sub

 

[Rick Rothstein \(MVP - VB\)]

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","12000"},0)),LEFT(A1,14)&CHOOSE(MATCH(RIGHT(A1,5),{"10000","11000","12000"},0),"10234","11003","12003"),A1)

Rick

 

[Ron Coderre]

Not a vba program, but....maybe this:
(in sections for readability)

=LEFT(A1,16)&CHOOSE(SUM(COUNTIF(A1,"*"&{"10","11","12"}&"000")*
{1,2,2})+1,RIGHT(A1,3),"234","003")


Is that something you can work with?
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Jeffrey]

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","12000"},0)),LEFT(A1,14)&CHOOSE(MATCH(RIGHT(A1,5),{"10000","11000","12000"},0),"10234","11003","12003"),A1)

Rick

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,

 

[Rick Rothstein \(MVP - VB\)]

Not sure how efficient it is, but I think this formula does what you

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,

You are welcome, but you might want to check out Ron's posted solution... it
looks like it might be more efficient as it doesn't call the active code
twice like mine does (once for the error checking and then again to make use
of it).

Rick