Splitting up text in a cell

[Mr Ellis]

I have a worksheet extracted from another program with financial data
in it. One of the columns contains information about what money has
been spent on. This consists of a category and a subcategory. For
example one cell might read "Bills : Electricity" or "Going out :
Restaurant". The category and the subcategory are always separated by
one space, a colon, then another space. What I'd like to do it
separate the category from the subcategory by using a formula that can
extract the text either before or after the space-colon-space bit.
Having looked at some of Excel's text functions I can't see an obvious
way to do this, any ideas?

 

[Mr Ellis]

I have a worksheet extracted from another program with financial data
in it. One of the columns contains information about what money has
been spent on. This consists of a category and a subcategory. For
example one cell might read "Bills : Electricity" or "Going out :
Restaurant". The category and the subcategory are always separated by
one space, a colon, then another space. What I'd like to do it
separate the category from the subcategory by using a formula that can
extract the text either before or after the space-colon-space bit.
Having looked at some of Excel's text functions I can't see an obvious
way to do this, any ideas?

OK, I think I've worked it out using a combination of LEN to find out
how long the text string is, SEARCH to find out where the colon is
within it, and LEFT and RIGHT to slice the string up based on those
values.

 

[isabelle]

hi,

=TRIM(LEFT(A1,SEARCH(":",A1)-1))

=TRIM(RIGHT(A1,LEN(A1)-SEARCH(":",A1)))

 

[Gord]

I would simply use Data>Text to Columns.


Gord Dibben Microsoft Excel MVP