Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 04 May 2021 01:07:19 +0200Replace 10,000.10 with 10.000,10https://ask.libreoffice.org/en/question/307602/replace-1000010-with-1000010/Imagine you are monitoring a stock price in a cell that autoupdates using the external data option, so you get 10,000.10, then after 60 seconds it updates and you get 10,020.10 or whatever.
My question is, how do I automatically get the stock price to update but switching the comma with a point and the point with a comma? because in my country we don't use 10,020.10 but 10.020,10 but the website im using gives you 10,020.10 so I need to replace them.
I've tried te substitute function but it's not working. Any helps?dellorTue, 04 May 2021 01:07:19 +0200https://ask.libreoffice.org/en/question/307602/Strip prefix text and sum the value?https://ask.libreoffice.org/en/question/291420/strip-prefix-text-and-sum-the-value/How do I write a formula that does something like this:
ITEM 1 8
ITEM 2 7
ITEM 3 C1
ITEM 4 3
ITEM 5 C2
ITEM 6 C7
TOTAL = 18 (NUMERICAL)
“C” TOTAL = 10 ←-- How to get that? What formula, please?
TimzLibCalcThu, 04 Feb 2021 21:51:37 +0100https://ask.libreoffice.org/en/question/291420/Substitute function in Calc being weirdhttps://ask.libreoffice.org/en/question/254168/substitute-function-in-calc-being-weird/ I am trying to convert lists of one and two-digit numbers into all two-digit numbers, so "1" would be "01" and so on. I tried adding leading zeroes, but for some reason it doesn't work when I entered the list, which was separated by colons. I get the feeling that even if I got that to work, it would turn my two digit numbers into three digit numbers, which I don't want ("012" and so on).
The other thing I tried was a nested SUBSTITUTE function:
SUBSTITUTE(SUBSTITUTE(Location,Search1,Replace1),Search2,Replace2))
This was fine until there were adjacent numbers that were the same. For instance "; 7; 10; 8; 9; 5; 8; 14; 5; 5; 13;" returns "; 07; 10; 08; 09; 05; 08; 14; 05; **5**; 13;". Notice that the lone 5 and the first 5 in the adjacent pair are corrected, but not the second in the pair.
Here is the exact formula I am using:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A$1,"; 1;","; 01;"),"; 2;","; 02;"),"; 3;","; 03;"),"; 4;","; 04;"),"; 5;","; 05;"),"; 6;","; 06;"),"; 7;","; 07;"),"; 8;","; 08;"),"; 9;","; 09;")
FaradnWed, 08 Jul 2020 02:57:27 +0200https://ask.libreoffice.org/en/question/254168/Migrating from Excel and indirect formula not working in Calchttps://ask.libreoffice.org/en/question/241698/migrating-from-excel-and-indirect-formula-not-working-in-calc/ I am trying to migrate away from Microsoft Office and use LibreOffice exclusively, however, I am running into a huge problem.
One of the formulas that I rely on is not working in LibreOffice Calc.
INDIRECT(SUBSTITUTE(E6," ","_"))
I am attempting to use this with data validity.
In column E6 there is data validity that pulls from our Business_Subsidiaries table. The returned business name is just like we would type it without the "_" in place of the spaces.
Where I am running into trouble is trying to use data validity in column F6. It needs to look at the business name at E6 and then return the data from the named table associated with that business.
What the spreadsheet sees is "Our Business" in location E6, but the actual table name is "Our_Business". When I select "cell range" under validity and input the above formula as the "source" I get a return of #REF!
It seems that the function "substitute" is not swapping " " for "_" when searching for the named table like it did in Excel.
How can I solve this other than turning the business names we see to Our_Business_1, Our_Business_2, and so on?
----------
SouthernWolfThu, 30 Apr 2020 22:14:09 +0200https://ask.libreoffice.org/en/question/241698/Using Substitute on text in calchttps://ask.libreoffice.org/en/question/148742/using-substitute-on-text-in-calc/I am trying to replace a piece of text within a cell while keeping a part of the original text.
Here is the original text that would be in one cell:
Find A Grave, database and images (https://www.findagrave.com : accessed 9 March 2018), memorial page for John Crawford Lynch, Jr (2 Nov 1898–10 Feb 1958), **Find A Grave Memorial no. 37178905**, citing Saint Thomas Episcopal Church Cemetery, Glassboro, Gloucester County, New Jersey, USA ; Maintained by bobo (contributor 47304269) .
I want to replace the portion in **bold** with "{{FindAGrave|37178905}}"
I tried using =SUBSTITUTE(C5,"Find A Grave Memorial no.","{{FindAGrave|") which works on the first part as long as I don't use the trailing space after no. If I use the trailing space within the quotes =SUBSTITUTE(C5,"Find A Grave Memorial no. ","{{FindAGrave|") the text doesn't change.
That still doesn't address the closing "}}".
I know I can do this with find and replace but ideally I would be able to just paste the original text in one cell and copy the text in the other cell for use where I need it.
OldJimFri, 09 Mar 2018 17:32:35 +0100https://ask.libreoffice.org/en/question/148742/Calc, =SUBSTITUTE (...) results in error. Why ?https://ask.libreoffice.org/en/question/76609/calc-substitute-results-in-error-why/ LibO 5.1.3.2
Cell A1 contains:
textstring(a)textstring
I want to replace "(a)" by "@"
Formula used in cell B1:
=SUBSTITUTE(A1,(a),@,1)
I am using comma as separators and created the formula with the wizard
Result:
#NAME?
which is "invalid name"
The string in A1 is text, verified by adding text "(a)@" using the formula:
=A1&"(a)@"
Result:
textstring(a)textstring(a)@
which is correct.
What is wrong? What do I need to do?
ROSt52Mon, 05 Sep 2016 16:43:13 +0200https://ask.libreoffice.org/en/question/76609/