Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to replace Advanced Filter | Excel Discussion (Misc queries) | |||
Advanced Find and replace | Links and Linking in Excel | |||
Advanced Find and Replace Question | Excel Discussion (Misc queries) | |||
Advanced Find and Replace Question | Excel Worksheet Functions | |||
find and replace - replace data in rows to separated by commas | Excel Worksheet Functions |