Can anyone help with a formuila for the following, I'm struggling .

I have a large number of text strings (in a column), many (not all) of which
contain 2 words or phrases separated by a colon, eg. ABC : DEF . I want
to split the string into two at the colon, giving 2 columns one with ABC and
the other with DEF. The colon itself is redundant. I need to be careful not
to corrupt those text strings which do not contain a colon, leaving the
2nd olumn blank in such cases.

This seems messy to me, any help would be appreciated

Thanks

K

On Mon, 29 Jun 2009 12:15:27 +0100, "KRK"
wrote:


Can anyone help with a formuila for the following, I'm struggling .

I have a large number of text strings (in a column), many (not all) of which
contain 2 words or phrases separated by a colon, eg. ABC : DEF . I want
to split the string into two at the colon, giving 2 columns one with ABC and
the other with DEF. The colon itself is redundant. I need to be careful not
to corrupt those text strings which do not contain a colon, leaving the
2nd olumn blank in such cases.

This seems messy to me, any help would be appreciated

Thanks

K



Look for "Text to columns" in your help file.
Assuming that there are no other colons than these separators in your
text it will be very easy to split the text into columns.
Use "delimited" and use colon as the delimiter.

Hope this helps / Lars-Åke

On Mon, 29 Jun 2009 12:15:27 +0100, "KRK"
wrote:

Can anyone help with a formuila for the following, I'm struggling .

I have a large number of text strings (in a column), many (not all) of which
contain 2 words or phrases separated by a colon, eg. ABC : DEF . I want
to split the string into two at the colon, giving 2 columns one with ABC and
the other with DEF. The colon itself is redundant. I need to be careful not
to corrupt those text strings which do not contain a colon, leaving the
2nd olumn blank in such cases.

This seems messy to me, any help would be appreciated

Thanks

K

Select your column

Then select the Data/Text to Columns wizard
Delimited
: as separator (you may have to type this into the Custom Box
--ron

Thanks,

this works nicely, and is rather simpler than the formula I was trying to
develop, which had Find, Iserror, Len, Left, Right functions embedded and
which was not working.

Thanks again

K

"Ron Rosenfeld" wrote in message
...
On Mon, 29 Jun 2009 12:15:27 +0100, "KRK"
wrote:

Can anyone help with a formuila for the following, I'm struggling .

I have a large number of text strings (in a column), many (not all) of
which
contain 2 words or phrases separated by a colon, eg. ABC : DEF . I want
to split the string into two at the colon, giving 2 columns one with ABC
and
the other with DEF. The colon itself is redundant. I need to be careful
not
to corrupt those text strings which do not contain a colon, leaving the
2nd olumn blank in such cases.

This seems messy to me, any help would be appreciated

Thanks

K

Select your column

Then select the Data/Text to Columns wizard
Delimited
: as separator (you may have to type this into the Custom Box
--ron

On Mon, 29 Jun 2009 13:16:35 +0100, "KRK"
wrote:

Thanks,

this works nicely, and is rather simpler than the formula I was trying to
develop, which had Find, Iserror, Len, Left, Right functions embedded and
which was not working.

Thanks again

You're welcome. Glad to help. Thanks for the feedback.
--ron