Ask Your Question

extract value from string

asked 2017-06-01 11:37:28 +0200

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

Have a column with variable length string. the last 4 to 8 chars represent a dollar value without the sign, but does contail the decimal. how do I extract value to a seperate column. Thank you. sample data

29/04/17 9663 Taronga Zoo Mosman Nsw 72.00
30/04/17 9663 Travel Reservation Au Sydney 357.75
30/04/17 9663 Travel Reservation Au Sydney 357.75
01/05/17 9663 Reed Holidays Forest Hill Vic 12,860.00

From data so far, it would seem the decimal point is unique to the value of interest, so could be used as a ref/locator. Not highly skilled in open office, calc, so would appreciate a specific comple command. thx

edit retag flag offensive close merge delete


What is the separator between the various fields - a tab?

robleyd gravatar imagerobleyd ( 2017-06-01 11:47:36 +0200 )edit

yes seperator is a tab. Input is copied from an online statement manually, leaving out everything except the financial lines. Special Pasted into a column, Calc offers tabs as separator, and I accept that. A normal paste gets all data into a single string into a single string. Thx

percy07 gravatar imagepercy07 ( 2017-06-01 23:35:09 +0200 )edit

colours seem to have been added by this system. i believe my data is a simple string but am not knowledgeable enough to confirm.

percy07 gravatar imagepercy07 ( 2017-06-01 23:45:18 +0200 )edit

I reformatted your post to make the sample data pre-formatted text to make it more readable - that has added the colouring.

robleyd gravatar imagerobleyd ( 2017-06-02 06:51:34 +0200 )edit

1 Answer

Sort by » oldest newest most voted

answered 2017-06-01 12:07:50 +0200

Just use Find & Replace to replace regular expression .*[^0-9,]([0-9,]+[.][0-9]+) with $1.

edit flag offensive delete link more


thx. I will try work through the find replace... Not sure I understand it by looking here. If I use .*[^0-9,]([0-9,]+[.][0-9]+) in the find box top of calc page and press enter...calc returns a message 'search key not found'

percy07 gravatar imagepercy07 ( 2017-06-01 23:48:34 +0200 )edit

You need to make sure that regular expressions are enabled in Find & Replace dialog.

Mike Kaganski gravatar imageMike Kaganski ( 2017-06-02 10:21:19 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-06-01 11:37:28 +0200

Seen: 484 times

Last updated: Jun 01 '17