Can anyone help me to find out how to use substitute function? Here is my
example:

Description: BANKCARD DES:MERCH SETL ID:1xxxxxxxxxxx,INDN:ABC COMPANY
CO ID:1AAAAAAAAA CCD

I'd like to get rid of "DES:MERCH SETL" and "CO". Here is my formula
=TRIM(SUBSTITUTE(SUBSTITUE(B6,"DES:MERCH SETL","","CO",""))

Any susgessions? Thanks.

hi,

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,"DES:MERCH SETL",""),"CO",""))

if this helps please click yes, thanks

"momotaro" wrote:

Can anyone help me to find out how to use substitute function? Here is my
example:

Description: BANKCARD DES:MERCH SETL ID:1xxxxxxxxxxx,INDN:ABC COMPANY
CO ID:1AAAAAAAAA CCD

I'd like to get rid of "DES:MERCH SETL" and "CO". Here is my formula
=TRIM(SUBSTITUTE(SUBSTITUE(B6,"DES:MERCH SETL","","CO",""))

Any susgessions? Thanks.

=TRIM(SUBSTITUTE(SUBSTITUTE(B6,"DES:MERCH SETL",""), "CO", ""))

You need to watch your brackets. Write one function and get it working and
then nest it in the next function...
--
HTH...

Jim Thomlinson


"momotaro" wrote:

Can anyone help me to find out how to use substitute function? Here is my
example:

Description: BANKCARD DES:MERCH SETL ID:1xxxxxxxxxxx,INDN:ABC COMPANY
CO ID:1AAAAAAAAA CCD

I'd like to get rid of "DES:MERCH SETL" and "CO". Here is my formula
=TRIM(SUBSTITUTE(SUBSTITUE(B6,"DES:MERCH SETL","","CO",""))

Any susgessions? Thanks.

I'd like to get rid of "DES:MERCH SETL" and "CO".
COMPANY CO

I assume you want to retain the "CO" in COMPANY.

Try this (all on one line):

=TRIM(SUBSTITUTE(SUBSTITUTE(A1
," DES:MERCH SETL ","")," CO ",""))


--
Biff
Microsoft Excel MVP


"momotaro" wrote in message
...
Can anyone help me to find out how to use substitute function? Here is my
example:

Description: BANKCARD DES:MERCH SETL ID:1xxxxxxxxxxx,INDN:ABC COMPANY
CO ID:1AAAAAAAAA CCD

I'd like to get rid of "DES:MERCH SETL" and "CO". Here is my formula
=TRIM(SUBSTITUTE(SUBSTITUE(B6,"DES:MERCH SETL","","CO",""))

Any susgessions? Thanks.

Does the text you want to remove appears in all records? Try using
Find/Replace but be careful in using it. Determine the unique text format so
that other texts may not be affected.

Sample:
FIND REPLACE
DES:MERCH SETL "leave blank"
_CO_ (_=space bar) "leave blank"


momotaro wrote:
Can anyone help me to find out how to use substitute function? Here is my
example:

Description: BANKCARD DES:MERCH SETL ID:1xxxxxxxxxxx,INDN:ABC COMPANY
CO ID:1AAAAAAAAA CCD

I'd like to get rid of "DES:MERCH SETL" and "CO". Here is my formula
=TRIM(SUBSTITUTE(SUBSTITUE(B6,"DES:MERCH SETL","","CO",""))

Any susgessions? Thanks.