OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register] 
 [Profile]   [Log in to check your private messages]   [Log in] 

Splitting a field?

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
scsijon
Newbie
Newbie


Joined: 24 May 2009
Posts: 4
PostPosted: Tue Feb 14, 2012 3:26 pm    Post subject: Splitting a field? Reply with quote
Problem – I want to be able to split the Names from the Versions of a number of software packages. The intention is that I end up with something like is listed below in the example, although I suspect there will need to be a number if interim fields. The – is the deliniator throughout all this, however there may be one or more groups of characters with deliniators in the name and the version may also have one or more deliniators in it, it should also usually only have numbers in it but may have git, r, or rc included.

I have some 30,000 lines to deal with so manually doing it is just not on!

I've tried a number of different ways, but failed miserable to even split them into individual fields using the – deliniator, could someone give me some help please.

Example:
Row1Column1:audacious-adplug-3.2-1
Row1Column2:audacious-adplug
Row1Column3:3.2-1

Listed below is a short group to give an idea of what I need to deal with.

thanks
scsijon

[list]
audacious-adplug-3.2-1
audacious-fluidsynth-3.2-1
audacious-plugins-3.2-1
audacious-pulse-3.2-1
audacious-sid-3.2-1
audacious-wavpack-3.2-1
audiokonverter-5.9.1-1
autopano-sift-C-2.5.1-3
avidemux-2.5.6-1
avidemux-cli-2.5.6-1
avidemux-gtk-2.5.6-1
avidemux-qt-2.5.6-1
Cdrdao-1.2.3-5
Cdrdao-toc2mp3-1.2.3-5
convlit-1.8-3
Faad2-2.7-2
fairymax-4.8-3
ffmpeg-0.10-1
freetype2-demos-2.4.8-2
gmusicbrowser-1.1.9-1
gpac-0.4.5-7
gstreamer0.10-a52dec-0.10.18-5
gstreamer0.10-amrnb-0.10.18-5
gstreamer0.10-amrwbdec-0.10.18-5
gstreamer0.10-cdio-0.10.18-5
gstreamer0.10-celt-0.10.22-6
gstreamer0.10-cog-0.10.22-6
gstreamer0.10-curl-0.10.22-6
gstreamer0.10-dc1394-0.10.22-6
gstreamer0.10-dirac-0.10.22-6
gstreamer0.10-directfb-0.10.22-6
gstreamer0.10-dts-0.10.22-6
gstreamer0.10-faad-0.10.22-6
gstreamer0.10-gme-0.10.22-6
gstreamer0.10-gsm-0.10.22-6
gstreamer0.10-jp2k-0.10.22-6
gstreamer0.10-kate-0.10.22-6
gstreamer0.10-ladspa-0.10.22-6
gstreamer0.10-lame-0.10.18-5
gstreamer0.10-libass-0.10.22-6
gstreamer0.10-mms-0.10.22-6
gstreamer0.10-mpeg-0.10.18-5
gstreamer0.10-mpeg2enc-0.10.22-6
gstreamer0.10-musepack-0.10.22-6
gstreamer0.10-nas-0.10.22-6
gstreamer0.10-neon-0.10.22-6
gstreamer0.10-ofa-0.10.22-6
gstreamer0.10-plugins-bad-0.10.22-6
gstreamer0.10-plugins-bad-doc-0.10.22-6
gstreamer0.10-plugins-ugly-0.10.18-5
gstreamer0.10-resindvd-0.10.22-6
gstreamer0.10-rsvg-0.10.22-6
gstreamer0.10-rtmp-0.10.22-6
gstreamer0.10-schroedinger-0.10.22-6
gstreamer0.10-sid-0.10.18-5
gstreamer0.10-soundtouch-0.10.22-6
gstreamer0.10-twolame-0.10.18-5
gstreamer0.10-vdpau-0.10.22-6
gstreamer0.10-voip-0.10.22-6
gstreamer0.10-vp8-0.10.22-6
gstreamer0.10-wildmidi-0.10.22-6
gstreamer0.10-x264-0.10.18-5
gstreamer0.10-xvid-0.10.22-6
gtkpod-2.1.0-3
k9copy-2.3.7-0.r565.1
lame-3.99.4-1
libavcodec53-0.10-1
libavfilter2-0.10-1
libavformat53-0.10-1
libavutil51-0.10-1
Libdca0-0.0.5-3
Libdca-devel-0.0.5-3
Libdca-tools-0.0.5-3
libdri-drivers-8.0-1
libdvdcss2-1.2.11-1
libdvdcss-devel-1.2.11-1
Libfaad2_2-2.7-2
libfaad2-devel-2.7-2
libfaad2-static-devel-2.7-2
libffmpeg-devel-0.10-1
libffmpeg-static-devel-0.10-1
libfreetype6-2.4.8-2
libfreetype6-devel-2.4.8-2
libfreetype6-static-devel-2.4.8-2
libglapi0-8.0-1
libglapi0-devel-8.0-1
libgpac0.4.5-0.4.5-7
libgpac-devel-0.4.5-7
libgpac-modules-0.4.5-7
libgstbasevideo0.10_0-0.10.22-6
libgstbasevideo-devel-0.10.22-6
libgstphotography0.10_0-0.10.22-6
libgstphotography-devel-0.10.22-6
libgstvdp0.10_0-0.10.22-6
libgtkpod1-2.1.0-3
libgtkpod-devel-2.1.0-3
liblame0-3.99.4-1
liblame-devel-3.99.4-1
liblame-static-devel-3.99.4-1
libmesaegl1-8.0-1
libmesaegl1-devel-8.0-1
libmesagl1-8.0-1
libmesagl1-devel-8.0-1
libmesaglesv1_1-8.0-1
libmesaglesv1_1-devel-8.0-1
libmesaglesv2_2-8.0-1
libmesaglesv2_2-devel-8.0-1
libmesaglu1-8.0-1
libmesaglu1-devel-8.0-1
libmesaglw1-7.11.2-5
libmesaglw1-devel-7.11.2-5
libmesaopenvg1-8.0-1
libmesaopenvg1-devel-8.0-1
libmyth0.24-0.24.2-1
libmyth-devel-0.24.2-1
libopal3.8.3-3.8.3-2
libopal3.8.3-plugins-3.8.3-2
libopal3-devel-3.8.3-2
Libopencore-amr0-0.1.2-3
Libopencore-amr-devel-0.1.2-3
libpano13_2-2.9.18-5
libpano13-devel-2.9.18-5
libpano13-tools-2.9.18-5
libpostproc52-0.10-1
libqmmp0-0.5.3-2
libqmmp-devel-0.5.3-2
libqmmpui0-0.5.3-2
libqmmpui-devel-0.5.3-2
libquicktime0-1.2.3-3
libquicktime-1.2.3-3
libquicktime-devel-1.2.3-3
libquicktime-dv-1.2.3-3
libquicktime-faad-1.2.3-3
libquicktime-lame-1.2.3-3
libquicktime-progs-1.2.3-3
libquicktime-x264-1.2.3-3
librtmp0-2.4-0.git20111228.1
librtmp-devel-2.4-0.git20111228.1
libswresample0-0.10-1
libswscaler2-0.10-1
libtxc-dxtn-1.0.1-2
libtxc-dxtn-devel-1.0.1-2
libvlc5-2.0.0-0.rc1.1
libvlccore4-1.1.13-1
libvlccore5-2.0.0-0.rc1.1
libvlc-devel-2.0.0-0.rc1.1
libvo-aacenc0-0.1.1-2
libvo-aacenc-devel-0.1.1-2
libvo-amrwbenc0-0.1.1-3
libvo-amrwbenc-devel-0.1.1-3
libx264_120-0.120-0.20111212.2
libx264-devel-0.120-0.20111212.2
libxine1-1.1.19-9
libxine1.2-devel-1.2.1-1
libxine2-1.2.1-1
libxine-devel-1.1.19-9
Libxvid4-1.3.1-2
Libxvid-devel-1.3.1-2
mednafen-0.9.18-2
mencoder-1.0-1.rc4.0.r34578.1
mesa-8.0-1
mesa-common-devel-8.0-1
mozilla-plugin-vlc-2.0.0-0.rc1.1
mpd-0.16.5-2
mplayer-1.0-1.rc4.0.r34578.1
mplayer-doc-1.0-1.rc4.0.r34578.1
mplayer-gui-1.0-1.rc4.0.r34578.1
mupen64plus-1.5-3
mythtv-backend-0.24.2-1
mythtv-doc-0.24.2-1
mythtv-frontend-0.24.2-1
mythtv-mythweb-0.24.2-1
mythtv-plugin-archive-0.24.2-1
mythtv-plugin-browser-0.24.2-1
mythtv-plugin-gallery-0.24.2-1
mythtv-plugin-game-0.24.2-1
mythtv-plugin-music-0.24.2-1
mythtv-plugin-netvision-0.24.2-1
mythtv-plugin-news-0.24.2-1
mythtv-plugin-video-0.24.2-1
mythtv-plugin-weather-0.24.2-1
mythtv-plugin-zoneminder-0.24.2-1
mythtv-setup-0.24.2-1
mythtv-themes-base-0.24.2-1
Null-5-2
ocaml-faad-0.3.0-2
ocaml-faad-devel-0.3.0-2
osmo4-0.4.5-7
perl-MythTV-0.24.2-1
python-mythtv-0.24.2-1
qmmp-0.5.3-2
qmmp-ffmpeg-0.5.3-2
qmmp-jack-0.5.3-2
qmmp-modplug-0.5.3-2
qmmp-musepack-0.5.3-2
qmmp-oss-0.5.3-2
qmmp-plugins-0.5.3-2
qmmp-wavpack-0.5.3-2
radiotray-0.7.2-1
rtmpdump-2.4-0.git20111228.1
svlc-2.0.0-0.rc1.1
transcode-1.1.5-12
Vdr-plugin-sc-0.9.3-2
vlc-2.0.0-0.rc1.1
vlc-plugin-aa-2.0.0-0.rc1.1
vlc-plugin-bonjour-2.0.0-0.rc1.1
vlc-plugin-common-2.0.0-0.rc1.1
vlc-plugin-dv-2.0.0-0.rc1.1
vlc-plugin-flac-2.0.0-0.rc1.1
vlc-plugin-fluidsynth-2.0.0-0.rc1.1
vlc-plugin-ggi-2.0.0-0.rc1.1
vlc-plugin-gme-2.0.0-0.rc1.1
vlc-plugin-gnutls-2.0.0-0.rc1.1
vlc-plugin-jack-2.0.0-0.rc1.1
vlc-plugin-kate-2.0.0-0.rc1.1
vlc-plugin-libass-2.0.0-0.rc1.1
vlc-plugin-libnotify-2.0.0-0.rc1.1
vlc-plugin-lirc-2.0.0-0.rc1.1
vlc-plugin-lua-2.0.0-0.rc1.1
vlc-plugin-mod-2.0.0-0.rc1.1
vlc-plugin-mpc-2.0.0-0.rc1.1
vlc-plugin-ncurses-2.0.0-0.rc1.1
vlc-plugin-opengl-2.0.0-0.rc1.1
vlc-plugin-projectm-2.0.0-0.rc1.1
vlc-plugin-pulse-2.0.0-0.rc1.1
vlc-plugin-schroedinger-2.0.0-0.rc1.1
vlc-plugin-sdl-2.0.0-0.rc1.1
vlc-plugin-shout-2.0.0-0.rc1.1
vlc-plugin-speex-2.0.0-0.rc1.1
vlc-plugin-svgalib-2.0.0-0.rc1.1
vlc-plugin-theora-2.0.0-0.rc1.1
vlc-plugin-twolame-2.0.0-0.rc1.1
vlc-plugin-upnp-2.0.0-0.rc1.1
vlc-plugin-zvbi-2.0.0-0.rc1.1
x264-0.120-0.20111212.2
xboard-4.5.3-3
xine1.2-common-1.2.1-1
xine-aa-1.1.19-9
xine-caca-1.1.19-9
xine-dxr3-1.1.19-9
xine-faad-1.1.19-9
xine-flac-1.1.19-9
xine-gnomevfs-1.1.19-9
xine-image-1.1.19-9
xine-jack-1.1.19-9
xine-plugins-1.1.19-9
xine-pulse-1.1.19-9
xine-sdl-1.1.19-9
xine-smb-1.1.19-9
xine-wavpack-1.1.19-9
zoneminder-1.25.0-6
[/list]
Back to top
View user's profile Send private message
range
General User
General User


Joined: 04 Jan 2012
Posts: 21
PostPosted: Tue Feb 14, 2012 4:01 pm    Post subject: Reply with quote
I think this might be what you require:

Code:
=LEFT(A1; SEARCH("-[0-9]"; A1)-1)
=RIGHT(A1; LEN(A1)-SEARCH("-[0-9]"; A1))

You have to enable regular expressions for the formulae to work...
Tools>Options>OpenOffice.org Calc>Calculate>Check "Enable Regular Expressions in Formulae"
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 208
PostPosted: Wed Feb 15, 2012 4:29 am    Post subject: Reply with quote
Hallo
Exactly you need:
Code:
=LEFT(A1;SEARCH("-[-0-9.]*$";A1)-1)

and
Code:
=MID(A1;SEARCH("-[-0-9.]*$";A1)+1;99)


Karo
Back to top
View user's profile Send private message
range
General User
General User


Joined: 04 Jan 2012
Posts: 21
PostPosted: Thu Feb 16, 2012 3:15 am    Post subject: Reply with quote
karolus wrote:
Hallo
Exactly you need:
Code:
=LEFT(A1;SEARCH("-[-0-9.]*$";A1)-1)

and
Code:
=MID(A1;SEARCH("-[-0-9.]*$";A1)+1;99)


Karo


Yes that is a better solution.

I think this might be suitable...

Code:
=LEFT(A1;SEARCH("-([-0-9.]|git|rc|r)*$";A1)-1)


Code:
=MID(A1;SEARCH("-([-0-9.]|git|rc|r)*$";A1)+1;99)
Back to top
View user's profile Send private message
karolus
OOo Advocate
OOo Advocate


Joined: 22 Jun 2011
Posts: 208
PostPosted: Thu Feb 16, 2012 4:00 am    Post subject: Reply with quote
Hallo

Oh, I see - there are some 'git' , 'rc' and 'r' in the version-number, but your formula go wrong too !
Better:
Code:
=LEFT(A1;SEARCH("-[-0-9.cgirt]*$";A1)-1)

and
Code:
=MID(A1;SEARCH("-[-0-9.cgirt]*$";A1)+1;99)


Karo
Back to top
View user's profile Send private message
scsijon
Newbie
Newbie


Joined: 24 May 2009
Posts: 4
PostPosted: Wed Apr 11, 2012 10:46 pm    Post subject: Reply with quote
absolute magic folks, that last one, with the addition of a couple of extra words after cgirt in the formula has sorted it nearly out for me.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group