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