| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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... |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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... |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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, |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nested String Search and Return Value. | Excel Discussion (Misc queries) | |||
| Nested if text search | Excel Discussion (Misc queries) | |||
| Limit to nested IF(ISNUMBER(SEARCH)) functions? | Excel Worksheet Functions | |||
| Can COUNTIF be nested to search two ranges | Excel Worksheet Functions | |||
| Search and Replace | Excel Worksheet Functions | |||
![Jeffrey[_9_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
