[Solved] Extract numbers from a text string

[imacanuck]

Hi all,

I'm looking for an easy way to extract the numbers from a column of cells. The pasted data contained in the cells is from multiple different users and does not follow a consistent format.

I have attached an example which shows the pasted data in Column A and the 2 results (input manually) in Columns C & D, that I am hoping to achieve.

Any help on this would be greatly appreciated.

Thanks.

[John_Ha]

As the data is so regular it can almost certainly be done with a regular expression search. See [Tutorial] How to record a macro (and Regular Expressions) for how to do regular expression searches.

I would test it by copying the data into Writer and doing the searching there. It will be tricky getting the expressions.

If you only have a few hundred, it may be quicker to do it manually using repeated Find and Replace something like below (I may have omitted a search but you get the idea)

1. Copy the data into Writer with Paste special > Unformatted.
2. Format it as Courier New so the columns line up so you can see what you are doing.
3. Edit > Find and Replace and change -1/2 to .5. Repeat for -1/4, -3/4 etc. It changes 5-1/2 to 5.5
4. Edit > Find and Replace and change 1/2 to 0.5. Repeat for 1/4, 3/4 etc. It changes 1/2 to 0.5
5. Edit > Find and Replace and change " to nothing
6. Save as a .csv file and import into Calc using x and space as the separators. This will put the numbers into columns.
7. Tidy up as some lines will be wrong (or tidy up using earlier Find and replace to delete unwanted text). It may help to sort the data so all similar wrong lines are together, make the changes, and then sort them back into the original order.

If you need to do it repeatedly, record a macro or macros to do the Find and Replaces.

This is the Writer .csv file after the above changes. I should also have changed " x " to "x" and "OD" to " OD" and deleted "T1-REG ".

Code: Select all

Pasted Text
1.5x4.25 Temp (Waterjet)
1.125x7 Temp
1.125x7 Temp
1.75x4.5
1x3.125
1x4.25 Temp
1x5.5 Temp
1x5.875 Temp
1x7 Temp
0.5 x 5.5OD
0.5 x 5.25OD
0.5x10.25 Temp
0.5x10.25 Temp
0.5x10.25 Temp
0.5x4.75
0.5x5.25
0.5x6.625
0.5x8
0.5x8 Temp
0.5x8 Temp
0.5x9.125 Temp
0.25x4.875 Temp
0.75x8.25
0.75x9.75
0.375x2
T1-Reg 1.75x5 Temp
T1-Reg 2.5x12 Temp
T1-Reg 3x11 Temp

Showing that a problem has been solved helps others searching so, if your problem is now solved, please view your first post in this thread and click the Edit button (top right in the post) and add [Solved] in front of the subject.

[karolus]

Hallo

Select the Data in Question but inclusive the ( EMPTY!! ) Cells right of them, and run extract_fractional_numbers

Code: Select all

import re

substitute = re.compile(r' |-|"').sub
rex = re.compile(r'([0-9+/]+)x([0-9+/]+)')
    
    
def repl(found):
    if found.group(0)=='-':
        return '+'
    else:
        return ''
    
    
def extract_fractional_numbers(*_):    
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    data = sel.DataArray
    out = []
    for row in data:
        match = rex.search(substitute(repl, row[0]))
        out.append((f'={match.group(1)}', f'={match.group(2)}' )) 
    sel.FormulaArray = out

afterwards apply the desired Numberformatcode # ??/16

this is python, to organize your python-scripts you should install apso.oxt from here

[Villeroy]

Itchy_Inches.ods

(34.04 KiB) Downloaded 147 times

[imacanuck]

Thanks for the help everyone. Very much appreciated.
I will play around with these suggestions.

Villeroy, clever filename for your work! Two thumbs up!!