EXCEL: Text to Columns

[GRitchie]

Here's one for you...

 

I have a spreadsheet with the following layout

 

[TABLE=width: 500]

[TR]

[TD]Forename

[/TD]

[TD]Surname[/TD]

[TD]Reg

[/TD]

[TD]Parental Consent

[/TD]

[/TR]

[TR]

[TD]Student[/TD]

[TD]One[/TD]

[TD]7D[/TD]

[TD]Internet Access, Photograph Student, School Visit[/TD]

[/TR]

[TR]

[TD]Student[/TD]

[TD]Two[/TD]

[TD]7A[/TD]

[TD]Photograph Student[/TD]

[/TR]

[/TABLE]

 

This is the way SIMS exports it.

I want to the split the text to columns.

That's easy enough.

 

 

The problem is, if the second student doesn't have 'Internet Access' the consent for 'Photograph Student' will be in the column where others have 'Internet Access'

If you understand? Like the following:

 

[TABLE=width: 500]

[TR]

[TD]Forename

[/TD]

[TD]Surname

[/TD]

[TD]Reg

[/TD]

[TD]Parental Consent

[/TD]

[TD]Parental Consent[/TD]

[TD]Parental Consent[/TD]

[/TR]

[TR]

[TD]Student[/TD]

[TD]One[/TD]

[TD]7D[/TD]

[TD]Internet Access[/TD]

[TD]Photograph Student[/TD]

[TD]School Visit[/TD]

[/TR]

[TR]

[TD]Student[/TD]

[TD]Two[/TD]

[TD]7A[/TD]

[TD]Photograph Student[/TD]

[TD][/TD]

[TD][/TD]

[/TR]

[/TABLE]

 

 

I want the columns to all follow the same format (column 1 = internet access, column 2 = photograph, column 3 = school visit)

 

 

Any ideas on how to do this??

SFTLP

[jdoyle]

leave the original column as is. Create columns for Internet Access, Photograph etc and put in a formula to search for text in the original column placing a 1 for Yes, 0 for No

[LosOjos]

I'd do the import as you have, then add in my own columns at the end for each type of Parental Consent and use a MATCH to check if they have that one. For example:

 

[table=width: 800]

[tr]

[td]Forename[/td]

[td]Surname[/td]

[td]Reg[/td]

[td]Parental Consent[/td]

[td]Parental Consent[/td]

[td]Parental Consent[/td]

[td]Internet Access[/td]

[/tr]

[tr]

[td]Student[/td]

[td]One[/td]

[td]7D[/td]

[td]Internet Access[/td]

[td]Photograph Student[/td]

[td]School Visit[/td]

[td]=IF(ISERROR(MATCH(G$1,$D2:$F2,0)),"No","Yes")[/td]

[/tr]

[tr]

[td]Student[/td]

[td]Two[/td]

[td]7A[/td]

[td]Photograph Student[/td]

[td][/td]

[td][/td]

[td]=IF(ISERROR(MATCH(G$1,$D3:$F3,0)),"No","Yes")[/td]

[/tr]

[/table]

 

That IF/MATCH combo would populate the end cell with "Yes" if that student has Internet Access permission, so the result would look like this:

 

[table=width: 800]

[tr]

[td]Forename[/td]

[td]Surname[/td]

[td]Reg[/td]

[td]Parental Consent[/td]

[td]Parental Consent[/td]

[td]Parental Consent[/td]

[td]Internet Access[/td]

[/tr]

[tr]

[td]Student[/td]

[td]One[/td]

[td]7D[/td]

[td]Internet Access[/td]

[td]Photograph Student[/td]

[td]School Visit[/td]

[td]Yes[/td]

[/tr]

[tr]

[td]Student[/td]

[td]Two[/td]

[td]7A[/td]

[td]Photograph Student[/td]

[td][/td]

[td][/td]

[td]No[/td]

[/tr]

[/table]

 

EDIT: altered it to use ISERROR, as I just remembered MATCH throws an error rather than a 0 if not found

Edited June 11, 2015 by LosOjos

[clareq]

I'm typing as I'm thinking here, so not fully sure if it's possible, but could you create three extra columns - Internet Access, Photograph Student and school visit, and then use some sort of look up so if the Parental consent box has the phrase "Internet Access" in it it puts a Y in the Internet Access column etc.?

 

I see someone better with Excel than I beat me to it

Edited June 11, 2015 by clareq

[pcstru]

Assuming the text for the attribute is consistent, I'd use the find function to check for the presence of the text and if find returned from the original column, put the attribute I want in the separated column. So :

 

=IF(ISERROR(FIND("",)),"","")

 

sort of thing.

Edited June 11, 2015 by pcstru

[bobsmith]

=ISNUMBER(SEARCH(E$1, $D2))

 

that'll do you nicely.

 

[TABLE=width: 797]

[TR]

[TD]Forename[/TD]

[TD]Surname[/TD]

[TD]Reg[/TD]

[TD]Parental Consent[/TD]

[TD]Internet Access[/TD]

[TD]Photograph Student[/TD]

[TD]School Visit[/TD]

[/TR]

[TR]

[TD]Student[/TD]

[TD]One[/TD]

[TD]7D[/TD]

[TD]Internet Access, Photograph Student, School Visit[/TD]

[TD=align: center]TRUE[/TD]

[TD=align: center]TRUE[/TD]

[TD=align: center]TRUE[/TD]

[/TR]

[TR]

[TD]Student[/TD]

[TD]Two[/TD]

[TD]7A[/TD]

[TD]Photograph Student[/TD]

[TD=align: center]FALSE[/TD]

[TD=align: center]TRUE[/TD]

[TD=align: center]FALSE[/TD]

[/TR]

[/TABLE]

[jdoyle]

 

 

As a slight change, you could search using the text in the column header.

 

 

Lastly, select the data created, Copy and then Paste Special/Paste as Values to overwrite the formulae with the values. Delete the original column..