LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
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.

Notices

Reply
 
LinkBack Search this Thread
Old 01-03-2011, 01:36 AM   #1
lothario
Member
 
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:

0.99
2.99
1.49
0.59
0.10

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

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

Code:
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
lothario
Member
 
Registered: Apr 2004
Posts: 340

Original Poster
Rep: Reputation: 30
Thanks Snark,

I will use this.
 
  


Reply


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
Trackbacks are Off
Pingbacks are On
Refbacks are 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


All times are GMT -5. The time now is 02:13 AM.

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