Urgent Help

[kiran]

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

 

[Pete_UK]

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

 

[Ian Grega]

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(",",A2,1)),IF(TYPE(FIND("-",A2,1))=16,100,FIND("-",A2,1)),IF(TYPE(FIND(":",A2,1))=16,100,FIND(":",A2,1)),IF(TYPE(FIND(";",A2,1))=16,100,FIND(";",A2,1)))-1))