Splitting up text in a cell

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?
 
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?

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top