advanced find and replace help
 

I want to replace sections between cells with a pipe symbol ' | ' but I cant
seem to get the function right. Because different data has different lengths
and characters, the find and replace function is too general. I eventually
want to merge the sections and place it in notepad when done.


e.g. I want to convert this:

AJE-1 NGA OML 113 AJE OML-1134 NIGERIA
N/A NGA OML 114 AJE OML-1113 NIGERIA

To this:

AJE-1|NGA|OML 113|AJE|OML-113|NIGERIA
N/A|NGA|OML 113|AJE|OML-113|NIGERIA

Any advice would be greatly appreciated.

Rich

advanced find and replace help
 

Hi

assuming the data is in column A, then in B1
=SUBSTITUTE(A1," ","|")
in C1
=SUBSTITUTE(B1,"OML|","OML ")
Copy down as far as required.

Column C will have your result. Copy column CPaste Special Values to "fix"
the data.
--
Regards
Roger Govier



"RJW" wrote in message
...
I want to replace sections between cells with a pipe symbol ' | ' but I
cant
seem to get the function right. Because different data has different
lengths
and characters, the find and replace function is too general. I eventually
want to merge the sections and place it in notepad when done.


e.g. I want to convert this:

AJE-1 NGA OML 113 AJE OML-1134 NIGERIA
N/A NGA OML 114 AJE OML-1113 NIGERIA

To this:

AJE-1|NGA|OML 113|AJE|OML-113|NIGERIA
N/A|NGA|OML 113|AJE|OML-113|NIGERIA

Any advice would be greatly appreciated.

Rich