New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Separate Text String Without Spaces Before/After Colon

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Separate Text String Without Spaces Before/After Colon

Postby Jarni » Mon Nov 07, 2016 10:51 pm

Hi All
I have a an exported set of Excel column headers with no-space text data in column C
The data in column C at the moment has to be manually separated

I need to separate the text with no spaces in one column into separated parts in order to run Pivot Tables

Here are some examples of the text in each cell in this column (about 800 rows) -
Absconded client:Whereabouts unknown
Medication:Error>wrong dosage
Missing person(s):Client missing>Whereabouts unknown
Behaviour:Self harm>Actual
Other:Motor Vehicle>Damage (accidental)>>minor

The text before the colon consists of the Category heading and will be placed into its own column - A
The text after the colon consists of the category detail type and will be placed into its own column - B

I have checked the Internet but have not found anything suitable – I am not an expert Excel user but only pedestrian
Is there a formula(s) or method which can extract the text as required please?

I have attached a file to show some of the data

Thank you for your help
  • 0

You do not have the required permissions to view the files attached to this post.
Jarni
Rookie
 
Posts: 6
Joined: Aug 18, 2015
Reputation: 0
Excel Version: Excel 2013

Re: Separate Text String Without Spaces Before/After Colon

Postby Beamer » Tue Nov 08, 2016 12:30 am

Hi Jarni, try this:

1 Select the cells with "joined" data
2 Click on the "Data" Tab, then Click "Text to Columns"
3 Make sure "Delimited" is selected and click "Next"
4 Place a tick in "Other", clear all other ticked boxes
5 Pace a ":" in the "Other" field box and click "Finish"

That should do the job for you.
  • 1

Beamer
Excel Junkie
 
Posts: 217
Joined: Oct 3, 2014
Location: New South Wales, Australia
Reputation: 31
Excel Version: 2010

Re: Separate Text String Without Spaces Before/After Colon

Postby Jarni » Sat Nov 12, 2016 7:29 pm

Hi Beamer
Sorry not to be able to get back here to reply till now
Thanks so much - worked perfectly - I will have to explore Text to Columns more and become familiar with its capabilities
Will save days of work Thank you again
  • 0

Jarni
Rookie
 
Posts: 6
Joined: Aug 18, 2015
Reputation: 0
Excel Version: Excel 2013

Re: Separate Text String Without Spaces Before/After Colon

Postby Beamer » Sun Nov 13, 2016 12:03 am

No problem, you're very welcome. I'm glad it worked for you.
  • 0

Beamer
Excel Junkie
 
Posts: 217
Joined: Oct 3, 2014
Location: New South Wales, Australia
Reputation: 31
Excel Version: 2010


Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 18 guests