Importing text+numeric data of varrying length into OO Calc

Discuss the spreadsheet application
Post Reply
User avatar
MarvinGomes
Banned
Posts: 1
Joined: Fri Oct 25, 2019 6:46 pm

Importing text+numeric data of varrying length into OO Calc

Post by MarvinGomes »

So I'm stuck on this seemingly simple task - I have formated text data that looks like this:

Text text number

Text number

Text text text number

And sadly for the life of me I don't know how to import it to open office calc in a reasonable manner - I want entire text from one line in one cell, and then number in the second cell (so the text is in one column and numbers in second) - any idea how do I do that? Sadly the separator between text and number is space, the same as between text and text, so setting 'separator' to space when importing won't work. I've tried looking up 10 different ways I could think of but nothing seems to work >_>
OpenOffice 3.1 on Windows Vista
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Importing text+numeric data of varrying length into OO C

Post by FJCC »

Without seeing your data it is difficult to be sure of the best path. My first guess is to import all of the into one column, use the SEARCH function to find the location of the number, and use the LEFT and RIGHT functions to pull out the two pieces. To provide details, we would need to see the data. Can you post an example of the data imported into one column os a spreadsheet? To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: Importing text+numeric data of varrying length into OO C

Post by RPG »

LibreOffice 7.1.4.2 on openSUSE Leap 15.2
JeJe
Volunteer
Posts: 2764
Joined: Wed Mar 09, 2016 2:40 pm

Re: Importing text+numeric data of varrying length into OO C

Post by JeJe »

If you open the file in Writer you can use the Find and Replace dialog to put whatever you want in front of the number

To find the numbers, in the search for box put:

\<[1-9][0-9]*\>

To add a comma in front in the Replace with box put:

,&

Change the comma with anything else you want instead.

Make sure regular expressions is ticked. and click replace all.
Windows 10, Openoffice 4.1.11, LibreOffice 7.4.0.3 (x64)
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Importing text+numeric data of varrying length into OO C

Post by RusselB »

If the number occurs after the last space, as shown in the examples, then use

Code: Select all

=VALUE(MID(A1;FIND(CHAR(0);SUBSTITUTE(A1;" ";CHAR(0);LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))));LEN(A1)))
OpenOffice 4.1.7, LibreOffice 7.0.1.2 on Windows 7 Pro, Ultimate & Windows 10 Home (2004)
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
Post Reply