[Solved] Full uppercase words from partial upper case

[Hadrilos]

Subject : Changing full uppercase words from partial upper case words

Hi !

I have in the same collumn these Names and First names and I would like to delete the names and then in lower case. But the Uppercase letter at the
begining of every First names mess with me and I have no idea how to resolve the problem.

ex :
SOLLOGOUB Nadia
SUEUR Jean-Pierre
SUTOUR Simon
TAILLÉ-POLIAN Sophie
TEMAL Rachid
TETUANUI Lana

Have a nice day

hadrien

[FJCC]

For the example data you provided, I would insert a blank column to the right of the data then select all of the cells you want to affect and choose the menu Data -> Text to Columns. Choose the Space as the delimiter and click OK. You will then have two columns with the first names separate from the surnames. Highlight the first names and use the menu Format -> Change Case -> Lower Case.

[RusselB]

Is your example what you are starting with, or what you want to end with?
If this is the data you are starting with, then please also provide an example of what you want to end with,
If it is what you want to end with, then please provide an example of what you are starting with.
If my interpretation of FJCC's reply is correct, then you could use a single extra column with the formula

Code: Select all

=LEFT(A1;FIND(" ";A1))&PROPER(MID(A1;FIND(" ";A1);LEN(A1)))

This presumes that the example shows what you want to end with.

[Lupp]

What about
DE GAULLE Charles
VAN DE GRAAFF Robert Jemison
?

Tasks concerning any kind of parsing depend on a syntactical specification. Otherwise we get stuck in guessing and the results will be doubtable which means BAD. You will not check every single result subsequently, will you?.
If you want to split your strings one position left of the first character for which upper case and lower case are different (lower case letter), please tell so.
I cannot easily imagine a different reasonable approach. If there is a different intention, describe it precisely.

[Hadrilos]

Hi !

Thanks for replying so fast. What I have now is :

What I have

What I want is :

What I want

Capture2.GIF (8.7 KiB) Viewed 1138 times

@Lupp, you are right, but yes i did remove all the inconvenient cells.

@JFCC, It was exactly the commandI was looking for. But it didn't work, I think that the space I see on the screen isn't a real space... I can select it but it doesn't respond as a space on the Text to Column Tool and it respond as a space when i ask for space in the Find Tool with the regular expressions checkbox activated.

When I delete this 'false space' and replace it with a spacebar space, it is recognized by the Column to Text Tool.

Is there any way in can turn all these false spaces into real one automatically ?

Thanks again for your answers.

[robleyd]

Use Find and Replace, copy the 'false space' into the Find text field.

Alternatively, as I assume you are importing this data from somewhere else, give us more information on how you are getting the data into Calc; it may be possible to fix the data as it comes into Calc.

[Hadrilos]

Thanks it worked !

I copied-pasted the list from : https://www.senat.fr/senateurs/senatl.html

Should I do it differently next time ?

Thanks again

[Lupp]

@Lupp, you are right, but yes i did remove all the inconvenient cells.

I don't remember a question concerning removal. But, well, I pointed out an expectable problem. Technical soulutions by software should be careful about expectable errors. On the other hand, a clearly correct solution strictly based on a syntax will not exist: There may occur given names written with only one letter (upper case then), others with two upper case characters at the beginning ... The inventiveness of people concerning strange things in writing will defeat any analytic approach. The only way to get rid of problems of the kind is to keep data under a heading, "a" meaning "an appropriat one". Never allow for compound data! To replace a sound "atomisation" (in the database sense) of data by means of formatting or uncommon writing is a specifically well proven way to hell. Using the upper case to mark a family name is a plus-nice idea insofar, as it not only aggravates separation of meaningful parts from the compound, but also has the potential to spoil the correct writing of the name.

Since I had pointed to a probable solution (not covering every case, however) I want to also demonstrate it. See attachment.

By the way: -1- Never post images in such a case, but usable data. -2- Make sure that "before" and "after" are correctly associated.

[robleyd]

I copied-pasted the list from https://www.senat.fr/senateurs/senatl.html

Should I do it differently next time ?

Indeed you should; the given and family names on that website are separated by non-breaking spaces, not normal spaces. I suspect this was done so the names wouldn't break onto two lines regardless of window resizing.

Next time, for that site use Paste Special, choose Unformatted text, and then choose Space as the Separator option. Sorry - I don't know what the French equivalents are. The result will be - mostly - family name in the first column and given name in the second column. You will have to tidy up a few such as de BELENET Arnaud.