Page 1 of 1
How to extract text and append it to another cell?
Posted: Mon Apr 15, 2019 4:28 am
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?
Re: How to extract text and append it to another cell?
Posted: Mon Apr 15, 2019 6:40 am
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
Re: How to extract text and append it to another cell?
Posted: Mon Apr 15, 2019 6:55 am
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.
Re: How to extract text and append it to another cell?
Posted: Mon Apr 15, 2019 9:42 am
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
Re: How to extract text and append it to another cell?
Posted: Mon Apr 15, 2019 10:57 am
by Villeroy
=LEFT(A1;FIND(":";A1)) with all desktop spreadsheets of the past 30 years. I don't use anything by Google.
Re: How to extract text and append it to another cell?
Posted: Mon Apr 15, 2019 11:52 am
by xuzo
Wow! Thanks , that works perfectly, now how can I get rid of the : , just http or https without the : ?
Re: How to extract text and append it to another cell?
Posted: Mon Apr 15, 2019 2:45 pm
by Zizi64
Wow! Thanks , that works perfectly, now how can I get rid of the : , just http or https without the : ?
Re: How to extract text and append it to another cell?
Posted: Mon Apr 15, 2019 4:55 pm
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.