advanced find and replace help

G

Guest

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
 
R

Roger Govier

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 C>Paste Special Values to "fix"
the data.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Find and Replace Cell Values 3
find and replace numeric strings in larger text strings 8
find and replace units of a formula 3
Help w/ Advanced Find and Replace 1
Find and Replace unwanted symbol 6
Find nth occurrence and replace with ":" 4
Find and Replace? 11
Find and Replace any digit 9

Top