Find and Replace
 

Hello,

The following text (or variation of it) is always in Cell H7 on a sheet
called "SetUp"
Some parts of this string are static, and others are updated as required.
I want to find and replace the text (which in this case is "FOODLIFE") with
other text.
The string "PRIMARY_VENDOR|^Method==|^2Val1=" is static, so if I can find
this, then replace whatever is after it, up until the beginning of the next
"|"


^1Enable=Y|^1Field=LOC_ID|^1Method==|^1Val1=41|^1W =V|^2Enable=Y|^2Optr=and|^2Field=PRIMARY_VENDOR|^2 Method==|^2Val1=FOODLIFE|^2W=V|^3Enable=Y|^3Optr=a nd|^3Field=BIN_1|^3Method=Starts|^3Val1=F|^4Enable =-|^4Optr=and|^4Field=DEPARTMENT|^4Method==|^4Val1=F ROZEN|^5Enable=-|^5Optr=and|^5Field=QTY_ON_HND|^5Method=<=|^5Val1= 0|^6Enable=-|^6Optr=and|^6Field=ITEM_NO|^6Method=Contains|^6Va l1=31863|^7Enable=-|^7Field=BRAND|^7Method=Contains|^8Enable=-|^9Enable=-|^10Enable=-|


Any ideas would be appreciated.

Thanks,

Ariel

Find and Replace
 

=SUBSTITUTE(H7,LEFT(MID(H7,SEARCH(H8,H7)+LEN(H8),L EN(H7)),SEARCH("|",MID(H7,SEARCH(H8,H7)+LEN(H8),LE N(H7)))-1),H9)
where in my example H8 contains the static part
H9 contains the other text

Regards,
Stefi


€žAD€� ezt Ã*rta:

Hello,

The following text (or variation of it) is always in Cell H7 on a sheet
called "SetUp"
Some parts of this string are static, and others are updated as required.
I want to find and replace the text (which in this case is "FOODLIFE") with
other text.
The string "PRIMARY_VENDOR|^Method==|^2Val1=" is static, so if I can find
this, then replace whatever is after it, up until the beginning of the next
"|"


^1Enable=Y|^1Field=LOC_ID|^1Method==|^1Val1=41|^1W =V|^2Enable=Y|^2Optr=and|^2Field=PRIMARY_VENDOR|^2 Method==|^2Val1=FOODLIFE|^2W=V|^3Enable=Y|^3Optr=a nd|^3Field=BIN_1|^3Method=Starts|^3Val1=F|^4Enable =-|^4Optr=and|^4Field=DEPARTMENT|^4Method==|^4Val1=F ROZEN|^5Enable=-|^5Optr=and|^5Field=QTY_ON_HND|^5Method=<=|^5Val1= 0|^6Enable=-|^6Optr=and|^6Field=ITEM_NO|^6Method=Contains|^6Va l1=31863|^7Enable=-|^7Field=BRAND|^7Method=Contains|^8Enable=-|^9Enable=-|^10Enable=-|


Any ideas would be appreciated.

Thanks,

Ariel