Download your favorite Linux distribution at LQ ISO.
Go Back > Forums > Linux Forums > Linux - Software
User Name
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.


  Search this Thread
Old 01-03-2011, 01:36 AM   #1
Registered: Apr 2004
Posts: 340

Rep: Reputation: 30
Openoffice: extract numerical value from text cell

I just received a spreadsheet.
Column B contains thousands of rows that look like this:

Giant Red Pencil 0.99
Case 2.99
Green Box 1.49
Ruler 0.59
Spiral Ring 0.10

So each cell contains the item and price.
I need the corresponding cells in column C to contain only the price like this:


How do I extract just the price from column B and put it in column C ?
Old 01-03-2011, 11:53 AM   #2
Senior Member
Registered: Sep 2010
Location: Wales, UK
Distribution: Arch
Posts: 1,632
Blog Entries: 3

Rep: Reputation: 346Reputation: 346Reputation: 346Reputation: 346
I've written you an OpenOffice Basic macro which should do this for you

Sub Modify

Dim Doc As Object
Dim Sheet As Object
Dim Cell As Object

Doc = ThisComponent
Sheet = Doc.Sheets(0)

Dim I                                             'These two variables are going to be our looping variables
Dim J
Dim Contents As String                            'Original contents of the cell
Dim Price As String                               'The price which we will extract
For I = 0 To 5                                    'Change this to the start and end rows you want to alter
	Cell = Sheet.getCellByPosition(1,I)       'Get the cell whose contents we're going to manipulate
	Contents = Cell.String                    'Set Contents to the data inside the cell
	For J=len(Contents) To 1 Step -1          'Search backwards in Contents for the first space (so the
		If MID(Contents,J,1) = " " Then   'rest of it contains the price)
			Exit For
		End If
	Next J

	Price = MID(Contents,J+1,len(Contents)-J) 'Get the everything in the string after the Jth character
	Cell = Sheet.getCellByPosition(2,I)       'Select the cell in column C
	Cell.String = Price                       'Set its contents to be the price we just extracted
Next I

End Sub
Hope this helps

Last edited by Snark1994; 01-03-2011 at 11:57 AM. Reason: Added code comments
1 members found this post helpful.
Old 01-04-2011, 02:34 AM   #3
Registered: Apr 2004
Posts: 340

Original Poster
Rep: Reputation: 30
Thanks Snark,

I will use this.


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Numerical encoding of text, by position danielbmartin Linux - Newbie 5 04-29-2010 12:31 AM
how to count the numerical digits in between the text using a command or a script? Kilam orez Linux - Newbie 9 01-03-2010 12:15 AM
Text file manipulation: Extracting specific rows according to numerical pattern CHARL0TTE Linux - Newbie 3 10-07-2009 07:14 AM
Extract certain text info from text file xmrkite Linux - Software 30 02-26-2008 11:06 AM
Cell formatting code in KSpread/OpenOffice montylee Linux - General 0 01-28-2006 11:21 AM > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 12:06 PM.

Main Menu
Write for LQ is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration