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