How to extract text and append it to another cell?

Discuss the spreadsheet application
Post Reply
xuzo
Posts: 7
Joined: Sun Apr 14, 2019 4:08 am

How to extract text and append it to another cell?

Post by xuzo »

I want to extract http prefix from a column and put it into another one.

With Google sheets I use this:

=REGEXEXTRACT(C:C, "https?")

WHat is the equivalent in Calc?
Windows 10, 64 Bit
Open Office 4.1.6
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to extract text and append it to another cell?

Post by Zizi64 »

Firstly,
you need study and compare the regular expressions reature of the Google sheet and the AOO/LO Calc:

https://github.com/google/re2/blob/mast ... syntax.txt
https://help.libreoffice.org/Common/Lis ... xpressions

The question mark means (when the regular expressions are switched on)...

- in Google sheets:
x? zero or one «x», prefer one

- in AOO/LO:
? Finds zero or one of the characters in front of the "?". For example, "Texts?" finds "Text" and "Texts" and "x(ab|c)?y" finds "xy", "xaby", or "xcy".

---

Secondary, here is an important information:
The ApacheOpenOffice can not address a column by reference "C:C".
You must use a real cellrange, for example "C1:C1000"

From version 5.0. of the LIBREOFFICE, it is possible to specify references to entire columns or rows using the A:A or 1:1 notation instead of A1:A1048576 or A1:AMJ1.
https://ask.libreoffice.org/en/question ... -eg-sumaa/

---

Finally:
My LibreOffice 6.2.2 has a function named REGEX(). But my AOO 4.1.5 has not. Try the LibreOffice and the REGEX function of the LO.
https://help.libreoffice.org/6.2/en-US/ ... regex.html
Last edited by Zizi64 on Mon Apr 15, 2019 7:14 am, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
RusselB
Moderator
Posts: 6646
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: How to extract text and append it to another cell?

Post by RusselB »

Without knowing what kind of information is returned from the formula you have given. my best guess is that a single simple formula does not exist.
That being said, I think a combination of SEARCH and MID will return what (again, I think) you are wanting.
I think you are wanting to extract a URL reference, based on your usage of https
If that is correct, and the text containing that string is in A1, then in B1 I would suggest using

Code: Select all

=mid(A1;search("https";A1;1);len(A1))
If that doesn't work for what you are wanting, please include a sample of the actual text you are starting with and the text that you want to end with.
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.
xuzo
Posts: 7
Joined: Sun Apr 14, 2019 4:08 am

Re: How to extract text and append it to another cell?

Post by xuzo »

Basically I want something like this:


[Table=]{ColumnA},{ColumnB}[/Table]
[Table=]{https://www.a.com},{https}[/Table]
[Table=]{https://www.b.com},{http}[/Table]

ColumnA ColumnB
https://www.a.com https
http://www.b.com http

So the http or https text is extracted/copied from ColumnA and pastes into ColumnB automatically
Windows 10, 64 Bit
Open Office 4.1.6
User avatar
Villeroy
Volunteer
Posts: 31264
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: How to extract text and append it to another cell?

Post by Villeroy »

=LEFT(A1;FIND(":";A1)) with all desktop spreadsheets of the past 30 years. I don't use anything by Google.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
xuzo
Posts: 7
Joined: Sun Apr 14, 2019 4:08 am

Re: How to extract text and append it to another cell?

Post by xuzo »

Wow! Thanks , that works perfectly, now how can I get rid of the : , just http or https without the : ?
Windows 10, 64 Bit
Open Office 4.1.6
User avatar
Zizi64
Volunteer
Posts: 11345
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: How to extract text and append it to another cell?

Post by Zizi64 »

Wow! Thanks , that works perfectly, now how can I get rid of the : , just http or https without the : ?

Code: Select all

=LEFT(A1;FIND(":";A1)-1)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Lupp
Volunteer
Posts: 3535
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: How to extract text and append it to another cell?

Post by Lupp »

It's generally a good idea to learn the basics concerning spreadsheet software to some depth using one program of the available crowd.

After that the most important concepts and the relevant wording should be known, and you won't be lost when switching to a different software for specific purposes, and needing adapted solutions in some cases.

Without knowing anything about text manipulation in spreadsheets it's difficult to adapt any formula grabbed from somewhere (even by just subtracting 1).

Learning by doing may work, but is an overestimated concept. Learning by asking is very good in school/courses, but limited otherwise.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply