[Solved] Which cell has the most characters? formula?

Discuss the spreadsheet application

[Solved] Which cell has the most characters? formula?

Postby dawidek990 » Wed Feb 19, 2020 5:09 pm

Hello, I have a question, I have 5 columns, and in each row of the column there is a given name as in the attachment.
What to do that in column G, or some other one next, fomula showed me which cell has the most characters, or most character ">" and showed or indexed it?
So something like: indicate which cell has the most characters, or character ">" and show it.
Attachments
kategorie chwilowy.ods
(14.04 KiB) Downloaded 21 times
Last edited by Hagar Delest on Thu Feb 20, 2020 12:20 am, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 55
Joined: Thu Dec 13, 2018 10:27 am

Re: Which cell has the most characters? formula?

Postby Lupp » Wed Feb 19, 2020 5:57 pm

There is a fundamental problem with your question: The ONE cell "with the most characters" will simply not exist if there are more than one cells containing the same maximum number of characters.
Acceptably simple means (formulas based on standard functions) will only return the first cell containing the maximum found. There may be more to the right (if matched in a row) or to the bottom (if matched in a column).
If more complexity is accepted, you still need to consider that a cell only can return ONE result. Returning more than one match in a cell would require to create something like a list in text form. AOO does not provide a standard function for this purpose.
Also see attachment. Errors and the "second-match-issue" are marked there by coloring.
Attachments
aoo101140findCellOfMaxTextlengthAndSo_1.ods
(19.57 KiB) Downloaded 16 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2980
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Which cell has the most characters? formula?

Postby dawidek990 » Wed Feb 19, 2020 6:15 pm

ok, is fine, formula can show me first finded row.
But now how i can index it? formuła show me d4 but i need cell content.
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 55
Joined: Thu Dec 13, 2018 10:27 am

Re: Which cell has the most characters? formula?

Postby keme » Wed Feb 19, 2020 8:37 pm

dawidek990 wrote:... how i can index it? ...

Use the INDEX() function
User avatar
keme
Volunteer
 
Posts: 3398
Joined: Wed Nov 28, 2007 10:27 am
Location: Egersund, Norway

Re: Which cell has the most characters? formula?

Postby Zizi64 » Wed Feb 19, 2020 9:20 pm

formuła show me d4 but i need cell content.


Try the function INDIRECT().
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.5;AOO4.1.7
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: 9702
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Which cell has the most characters? formula?

Postby Lupp » Wed Feb 19, 2020 9:47 pm

Omitting the ADDRESS() function and instead passing the MATCH() result to the INDEX() function as the column index should be rather efficient.
I only used ADDRESS() in the preliminary solution to ease the comparison with what the OQer already had entered by hand.
Attachments
aoo101140findCellOfMaxTextlengthAndSo_2.ods
(19.87 KiB) Downloaded 15 times
On Windows 10: LibreOffice 7.0 and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2980
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Which cell has the most characters? formula?

Postby dawidek990 » Wed Feb 19, 2020 9:55 pm

INDIRECT() is the best and most simple function ;]
is working, thank you very much ;]
OpenOffice 4.1.6 Windows 10
dawidek990
 
Posts: 55
Joined: Thu Dec 13, 2018 10:27 am


Return to Calc

Who is online

Users browsing this forum: No registered users and 28 guests