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 >_>
Importing text+numeric data of varrying length into OO Calc
- MarvinGomes
- Banned
- Posts: 1
- Joined: Fri Oct 25, 2019 6:46 pm
Importing text+numeric data of varrying length into OO Calc
OpenOffice 3.1 on Windows Vista
Re: Importing text+numeric data of varrying length into OO C
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.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Importing text+numeric data of varrying length into OO C
I think a copy of Importing text+numeric data of varrying length into OO Calc (lil help?) : openoffice
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Re: Importing text+numeric data of varrying length into OO C
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.
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)
Re: Importing text+numeric data of varrying length into OO C
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.
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.