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?
How to extract text and append it to another cell?
How to extract text and append it to another cell?
Windows 10, 64 Bit
Open Office 4.1.6
Open Office 4.1.6
Re: How to extract text and append it to another cell?
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
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.
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.
Re: How to extract text and append it to another cell?
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
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.
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))
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.
Re: How to extract text and append it to another cell?
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
[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
Open Office 4.1.6
Re: How to extract text and append it to another cell?
=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
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: How to extract text and append it to another cell?
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
Open Office 4.1.6
Re: How to extract text and append it to another cell?
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.
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.
Re: How to extract text and append it to another cell?
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.
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
---
Lupp from München