Nested Search and Replace

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeffrey - 12 Mar 2008 20:57 GMT

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

Reply to this Message

Gary''s Student - 12 Mar 2008 21:27 GMT

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

Signature

Gary''s Student - gsnu200773

> I have a worksheet with one column which contains numerous GL account
> codes suchs as:
[quoted text clipped - 25 lines]
>
> Any thoughts how I can accomplish this with a formula...

Reply to this Message

Rick Rothstein (MVP - VB) - 12 Mar 2008 22:21 GMT

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

>I have a worksheet with one column which contains numerous GL account
> codes suchs as:
[quoted text clipped - 25 lines]
>
> Any thoughts how I can accomplish this with a formula...

Reply to this Message

Jeffrey - 13 Mar 2008 15:37 GMT

On Mar 12, 3:21 pm, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.S...@NO.SPAMverizon.net> 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)...
[quoted text clipped - 32 lines]
>
> > 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,

Reply to this Message

Rick Rothstein (MVP - VB) - 13 Mar 2008 16:01 GMT

>> Not sure how efficient it is, but I think this formula does what you
>> asked
[quoted text clipped - 37 lines]
> 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

Reply to this Message

Ron Coderre - 12 Mar 2008 22:36 GMT

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)

>I have a worksheet with one column which contains numerous GL account
> codes suchs as:
[quoted text clipped - 25 lines]
>
> Any thoughts how I can accomplish this with a formula...

Reply to this Message