SIMS: SURNAME Forename, from Marksheet convert to columns

[HughR]

I've tried to do this before and there are various ways of doing it. I usually: 1) Export to Excel, 2) 'Text to Columns', 3) Copy/Paste the SURNAME into Word, 4) Ctrl-A (Select All), 5) Shift-F3 toggle until correct format achieved, 6) Cut-Paste back into Excel 7) Tidy up and CONCATENATE as appropriate.

 

I've tried doing this with Formulae before, and struggled. I have now managed to put together a few formulae that work EXACTLY as they should do, so I thought I would share, as I expect others have the same issue (N.B. I know you can do this using a SIMS Report, but sometimes you will want to work directly with the output from a marksheet, and this just makes it easier if you want the Surname and Forename columns reformatting).

 

Column A [starting A1], contains: 'SURNAME Forname' e.g. MOUSE Mickey, DUCK Donald. Don't bother with Headers.

 

In Column B (B1), Paste in the following Formula: (N.B. This is an ARRAY formula, so you will probably need to enter it using Shift-Ctrl-Enter, it will end up parenthesised within {curly brackets}.

 

=MID(A1,MATCH(1,(CODE(MID(A1,ROW($1:$255),1))>=97) *(CODE(MID(A1,ROW($2:$255),1))<122),)-1,255)

 

In Column C,(C1) Paste in the following Formula:

 

=PROPER(LEFT(A1,D1))

 

In Column D, (D1) Paste in the following Formula: You can hide this column afterwards. (N.B. This is an ARRAY formula, so you will probably need to enter it using Shift-Ctrl-Enter, it will end up parenthesised within {curly brackets}.)

 

=MATCH(1,(CODE(MID(A1,ROW(A$1:A$255),1))>96)*(CODE(MID(A1,ROW(A$1:A$255),1))<123),0)-3

 

Copy DOWN, B1, C1 and D1 to the bottom of your Name list. Save and store for future use.

 

I hope this is useful to you! If you have an easier way of doing this, perhaps you could post below! I would like to claim that this is all my own work, but I have put this together from a number of different sources and adjusted to suit.

 

Cheers,

 

Hugh

 

Sources:

 

https://excel.tips.net/T009089_Splitting_Cells_by_Case.html

https://www.excelbanter.com/excel-discussion-misc-queries/247561-split-cell-based-uppercase-text.html

Find Position of First Capital Letter in a String – Daily Dose of Excel

https://satyadipan.blogspot.com/2016/10/c-program-to-convert-uppercase-letter.html

https://superuser.com/questions/1302988/split-cells-by-uppercase-and-lowercase-words-in-excel

Edited June 4, 2019 by HughR

[Seb1780]

If column A contains SURNAME Forename I use the FIND() function to locate the space and then:-

 

In column B =PROPER(LEFT(A2,FIND(" ",A2)-1))

In column C =RIGHT(A2,LEN(A2)-FIND(" ",A2))

Edited June 4, 2019 by Seb1780

[mavhc]

Better to use Proper to cope with Unicode. Also watch out for hyphens and apostrophes in names

[HughR]

Ok, testing this... Edited June 4, 2019 by HughR

[HughR]

@Seb1780

 

This is certainly a lot more elegant than mine... however when I tested it, it threw up a few issues e.g. If the Surname contained more than one name (hyphenated names were fine) the output was a little off:

 

(names changed)

 

JONES MALLOY Evan (output was: MALLOY Evan Jones)

SMITH HERBERT DONNELLY Peter David (output was: Smith HERBERT DONNELLY Peter David)

 

A lot of this has to do with the way that the student data has been put into SIMS, but a lot of our (mainly Arabic) names have multiple (and inconsistent) components.

 

My formula above does what I want it to do, but yes, messing about with unicode, ASCII conversions back and forth, is messy.

 

Could your formulas be adjusted to compensate do you think?

 

***Edit*** I suppose the issue is multiple spaces.

Edited June 4, 2019 by HughR

[Seb1780]

A lot of this has to do with the way that the student data has been put into SIMS, but a lot of our (mainly Arabic) names have multiple (and inconsistent) components.

 

 

***Edit*** I suppose the issue is multiple spaces.

 

Yep, it's got to be good data in SIMS first.

 

The formula will only find the first space, you could cascade it to split all components of the name but you would not know which was part of the surname and which was part of the forename.

 

For us the number of double barrelled surnames is small, and the number without hyphens are smaller still. A year group of 250+ kids might need manual intervention for two or three pupils. I can live with this

Edited June 4, 2019 by Seb1780

[HughR]

Yep, it's got to be good data in SIMS first.

 

For us the number of double barrelled surnames is small, and the number without hyphens are smaller still. A year group of 250+ kids might need manual intervention for two or three pupils. I can live with this

 

Ha ha! For us it's the opposite.

 

Thanks for the formula though - I'll have a go at adjusting it

[mavhc]

Surely easier to fix the source report. What's with putting surnames in caps in the first place anyway?

[HughR]

Surely easier to fix the source report. What's with putting surnames in caps in the first place anyway?

 

It's the Default output on a SIMS marksheet. If there was a way to force two separate columns on a marksheet (i.e. Column 1: Surname, Column 2: Forename), that would make the whole thing unnecessary, but SIMS has been this way for at least the last 10 years, so it's likely a legacy thing.

Edited June 5, 2019 by HughR

[Seb1780]

It's SIMS

 

FTFY

[HughR]

FTFY

 

Perfectly summarised!

 

Capita would probably say "It's a feature..."

[mavhc]

Guess the main problem is it doesn't export the comma, that seems like a bug. Bet they removed it because it broke csv

 

Still no idea why people put surnames in caps though

[HughR]

FTFY

 

Guess the main problem is it doesn't export the comma, that seems like a bug. Bet they removed it because it broke csv

 

Still no idea why people put surnames in caps though

 

Yeah, the comma would help!

 

I think that SIMS itself converts the Surname to capitals for the 'benefit' of the marksheets. In the student record, it's fine.

 

Like, I say Capita would probably call it a 'feature' (I'd call it something less polite!).

[clareq]

Does this help? https://www.extendoffice.com/documents/excel/5386-excel-find-first-lowercase-letter.html

It returns the position in the string of the first Lower case letter - with that you can subtract 1 to find the start of the Forename and split there.