View original (French)

LibreOffice Calc: extract part of a cell

Solved
barnabe0057 Posted messages 14431 Registration date   Status Contributeur Last intervention   -  
Francois78000 Posted messages 2 Status Membre -
Hello,

I'm looking for a way to extract part of a cell value, let me explain:

In a spreadsheet, I have a column containing values like l+k://coordinates?16418,15949&20
the numbers 16418 and 15949 represent x and y coordinates, I would like to extract them and put them next door in two separate columns named x coordinate and y coordinate.

I'm using LibreOffice 3.6.5.2

Thank you in advance for your help.

Configuration: Win 7 Pro 64bits
Athlon 4800+ Dual Core
2 GB DDR2 800 MHz

4 réponses

Réponse 1 / 4
tontong Posted messages 2575 Registration date   Status Membre Last intervention   1 064
 
Hello,
According to the constant elements, the formulas will be more or less long.
Example =MID(A2,19,5) returns the 5 characters following the 19th, so 16418.
Which elements are constant? Which elements vary?
8
Francois78000 Posted messages 2 Status Membre
 
Hello Tontong,
I have a similar problem to Barnabe0057, except that I don't have the same constant elements:
/formation-petite-enfance-Vieux-port-27680
/formation-petite-enfance-Villaines-la-juhel-53700

The constant element is the 5 digits that are always at the end of the line,
I would like to extract these 5 digits (they are always 5) at the end of the line and copy them to the next column.
0
tontong Posted messages 2575 Registration date   Status Membre Last intervention   1 064 > Francois78000 Posted messages 2 Status Membre
 
Hello,
Just enter the formula =RIGHT(A1,5) in B1 and drag down.
If an unwanted space is disrupting the text at the beginning or end, use the formula:
=RIGHT(TRIM(A1),5)
1
Francois78000 Posted messages 2 Status Membre > tontong Posted messages 2575 Registration date   Status Membre Last intervention  
 
Thank you very much, it works perfectly!
0
Réponse 2 / 4
barnabe0057 Posted messages 14431 Registration date   Status Contributeur Last intervention   4 929
 
Comment automatiser votre formule sur l'ensemble de ma colonne ?

Parce que je ne me vois pas taper la formule 10 000 fois :
B2=RIGHT(A2, 5)
B3=RIGHT(A3, 5)
B4=RIGHT(A4, 5)
etc ...
2
tontong Posted messages 2575 Registration date   Status Membre Last intervention   1 064
 
Using the fill handle: it's the small black square at the bottom right of the cell.
Hover the pointer over it, hold down the left click, and drag down.
If the adjacent column to the left contains values, you can also double-click on this fill handle.
0
barnabe0057 Posted messages 14431 Registration date   Status Contributeur Last intervention   4 929
 
Okay, I have to step out, I'll try that tonight.

I'll keep you updated.
0
Réponse 3 / 4
barnabe0057 Posted messages 14431 Registration date   Status Contributeur Last intervention   4 929
 
Thank you for your response, the length of the values is fixed, and the only elements that vary in these values are the two numbers corresponding to the coordinates.

So your formula is perfect ;)
0
Réponse 4 / 4
barnabe0057 Posted messages 14431 Registration date   Status Contributeur Last intervention   4 929
 
It works very well, thank you so much for your valuable help!

Have a great day, see you!
0