Splitting cells down in excel

spadoosh

Wondering if anyone can help?

I get a report that contains quite a bit of information in one cell. Ive seen someone break down the contents of the cell in to specific information.

As an example the cell might contain (surname, name (id) date) in that format. How would i go about breaking it down in to a cell for each bit of information?

Thanks for any advice!

Its office 2010 if that makes a difference?

uknick

If the data in the cell is always in the same format i.e.

aaaabbbcddddd

you can use the MID function, it pulls out characters starting at the specified start point for so many characters

e.g. taking the above as an example

MID(cellref,1,4) will return aaaa
MID(cell ref,5,3) will return bbb etc..

Ainsley1

Firstly just to clarify (one needs to be quite specific!)
You get sent as an Excel file?
Each sell has identical format with all bits populated the same:
Surname
Comma
Space
Name (is that forename?)
Space
Id brackets
Space
Date (format of date? Is it text?)

It would help if you could post back a line with a cell's content (change the details if necessary for anonymity )

It might also be useful if you could indicate if the Id is and will remain fixed length?

Is there other data in cells as it can be easy sometimes to actually export the data and then import with defined limiters back into a sheet.

davomcdave

Is there something that separates the separate pieces of datum like a comma or a space? If so you use the 'text to columns' function.

spadoosh

@ ainsley1

The cell contents are more or less as you say.
I wont type space but assume spaces are accurate.

row 1 [Potter, Harry (ab1234) 11/01/2017 to 12/01/2017]
row 2 [Granger, Hermione (123456789) 11/01/2017 to 12/01/2017]

The id (ab1234) can be variable in length and characters, some are numeric some are alphanumeric and can vary from 5 - 10 characters

Its sent as a .tsv file opened in excel.

Not sure what is meant by the last bit, there is other data in other cells but nothing that can be used as an identifier.

Im basically trying to print it in an easier to view format and the cell length takes it past printing border (incl landscape). I normally go through each row to delete date and ID but its tedious going through 150+ rows



@ Uknick, im not sure that would work as there will be variants in sur/name lengths and the ID number.


Thank you both for your replies.

spadoosh

davomcdave wrote: »

Is there something that separates the separate pieces of datum like a comma or a space? If so you use the 'text to columns' function.

Thatll do!!

Thanks!

Andy_L

you could use the find function to get the position of the brackets & then use the position number in (as uknick said), the mid function and/or left & right functions to extract the name & dates into their own columns

Le_Kirk

If you receive a .tsv (tab separated value file), you could open it in Excel by using the parse function which will allow you to select the tabs and open the file so that each section of tab separated information is in a different cell along the row.

Zola.

Le_Kirk

^^^ No you lost me!!!

Ainsley1

The reason I was asking about the type of file you get as my first question was thinking along the lines LeKirk has replied.

For some file types Excel handles them very well as data imports such as fixed field widths (not yours) or field delimited by specific 'characters' such as space, comma or Tab separated files (such as your .tsv file).

You can set delimiters between data fields (tabs in your case) that applies to each record. Thus you get each record's field (name, Id etcetera) in separate cells in a row. Far better to do as one activity that can be replicated in the future, splitting the record as you desire rather than what the excel functions assumes across the record.

A video on how to do it (if you can stand the USA drawl !) for version 2010 at https://m.youtube.com/watch?v=OriqzXqLcaw
Other versions of Excel are similar.