Splitting up text in a cell

  • Thread starter Thread starter Mr Ellis
  • Start date Start date
M

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?
 
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?
Click to expand...

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.
 
Back
Top