How to extract text and append it to another cell?

Discuss the spreadsheet application

How to extract text and append it to another cell?

Postby xuzo » Mon Apr 15, 2019 4:28 am

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
xuzo
 
Posts: 4
Joined: Sun Apr 14, 2019 4:08 am

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

Postby Zizi64 » Mon Apr 15, 2019 6:40 am

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; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8357
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby RusselB » Mon Apr 15, 2019 6:55 am

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   Expand viewCollapse view
=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 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
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.
User avatar
RusselB
Moderator
 
Posts: 5507
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

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

Postby xuzo » Mon Apr 15, 2019 9:42 am

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
xuzo
 
Posts: 4
Joined: Sun Apr 14, 2019 4:08 am

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

Postby Villeroy » Mon Apr 15, 2019 10:57 am

=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, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 27225
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Postby xuzo » Mon Apr 15, 2019 11:52 am

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
xuzo
 
Posts: 4
Joined: Sun Apr 14, 2019 4:08 am

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

Postby Zizi64 » Mon Apr 15, 2019 2:45 pm

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


Code: Select all   Expand viewCollapse view
=LEFT(A1;FIND(":";A1)-1)
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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
Zizi64
Volunteer
 
Posts: 8357
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

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

Postby Lupp » Mon Apr 15, 2019 4:55 pm

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 6.2 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2533
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany


Return to Calc

Who is online

Users browsing this forum: keme and 18 guests