ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Nested Search and Replace (https://www.excelbanter.com/excel-programming/407597-nested-search-replace.html)

Jeffrey[_9_]
Nested Search and Replace
 
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
Nested Search and Replace
 
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

--
Gary''s Student - gsnu200773


"Jeffrey" wrote:

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

Rick Rothstein \(MVP - VB\)[_1438_]
Nested Search and Replace
 
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...


Ron Coderre
Nested Search and Replace
 
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" 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...



Jeffrey[_9_]
Nested Search and Replace
 
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,

Rick Rothstein \(MVP - VB\)[_1452_]
Nested Search and Replace
 
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,

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



All times are GMT +1. The time now is 08:57 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com