Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Urgent Help
Hi All,
I have data in column A as follows iut data wexpro, data cas - factory pondy-sales coffee:beverages fact I want my output in B column as follows A B iut data iut wexpro, data wexpro cas - factory cas pondy-sales pondy coffee:beverages fact coffee TIA |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Urgent Help
First of all, highlight column A and apply Find & Replace (CTRL-H)
three times as follows: Find What: <hyphen Replace with <space<hyphen Replace All CTRL-H Find What: <colon Replace with <space<colon Replace All CTRL-H Find What: <comma Replace with <space<comma Replace All Then you can apply Data | Text-to-Columns using <space as the delimiter and choosing to skip any columns beyond the first one and to write the data to B1 on the final panel. Hope this helps. Pete On Apr 28, 4:59*pm, kiran wrote: Hi All, I have data in column A as follows iut data wexpro, data cas - factory pondy-sales coffee:beverages fact I want my output in B column as follows * *A * * * * * * * * * * * * * * * * * B iut data * * * * * * * * * * * * * * iut wexpro, data * * * * * * * * * * wexpro cas - factory * * * * * * * * * * *cas pondy-sales * * * * * * * * * * * pondy coffee:beverages fact * * * * coffee TIA |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Urgent Help
The following equation works but there is a probably an easier way with the
code or character set or a macro. But this equation does work and I have added a semi-colon to the set of characters to search for, other characters may be added. =MID(A2,1,(MIN(IF(TYPE(FIND(" ",A2,1))=16,100,FIND(" ",A2,1)),IF(TYPE(FIND(",",A2,1))=16,100,FIND(",",A 2,1)),IF(TYPE(FIND("-",A2,1))=16,100,FIND("-",A2,1)),IF(TYPE(FIND(":",A2,1))=16,100,FIND(":",A 2,1)),IF(TYPE(FIND(";",A2,1))=16,100,FIND(";",A2,1 )))-1)) "Pete_UK" wrote: First of all, highlight column A and apply Find & Replace (CTRL-H) three times as follows: Find What: <hyphen Replace with <space<hyphen Replace All CTRL-H Find What: <colon Replace with <space<colon Replace All CTRL-H Find What: <comma Replace with <space<comma Replace All Then you can apply Data | Text-to-Columns using <space as the delimiter and choosing to skip any columns beyond the first one and to write the data to B1 on the final panel. Hope this helps. Pete On Apr 28, 4:59 pm, kiran wrote: Hi All, I have data in column A as follows iut data wexpro, data cas - factory pondy-sales coffee:beverages fact I want my output in B column as follows A B iut data iut wexpro, data wexpro cas - factory cas pondy-sales pondy coffee:beverages fact coffee TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
urgent help | Excel Discussion (Misc queries) | |||
#ARG! - urgent | Excel Discussion (Misc queries) | |||
urgent help | Excel Discussion (Misc queries) | |||
Urgent-Urgent VBA LOOP | Excel Discussion (Misc queries) | |||
not urgent | Excel Worksheet Functions |