0

I'm using Google Sheets.

Let's say I have a cell whose value is a42c04ae-ea06-4992-b0bf-254943a7fdc2.

And I want it's column to be somewhat narrow, where unfortunately I won't be able to see the whole string.

I know if I right-align the text, I would be guaranteed to see the final characters.

But I want to be guaranteed to see both the first and last characters.

How can I make its display contain as many characters as possible symmetrically from the beginning and end but then show an ellipsis (...) in the middle as necessary (to indicate that some characters were omitted from the display)?

I'd want it to automatically adjust whenever anyone widens or narrows the column.

2 Answers 2

2

I think you can get a little bit closer to your original request with the following:

=arrayformula(let(
strings,tocol($A$2:$A,1),
strlen,(cell("width",$B$2)/2)-1,
abbrev,left(strings,strlen)&"…"&right(strings,strlen),
if(now(),abbrev,)))

I'm assuming that your source strings are in A2:A, and (like doubleunary's answer) you hide the column. This formula goes in B2. By using the width argument of the CELL function on the formula cell itself (which is allowed and does not cause a circular reference error), we can determine the current width of the formula cell and tailor the length of the abbreviated strings generated accordingly. If you resize the column, the abbreviated strings will be updated after a change is made elsewhere on the sheet (minimally a backspace on any empty cell will be enough as this is sufficient to trigger a recalculation), via the if(now() hack.

CELL's width argument is a bit weird as it is based on the width of the zero character of the default font at the default size, so if you are using anything other than this you may need to tweak the strlen argument. Also, the use of variable width fonts rather than monospaced ones may lead to suboptimal results if the strings contain many narrow characters.

3
  • Thank you! This seems like it has amazing potential, but as shown at docs.google.com/spreadsheets/d/… I don't see it adjusting based on the column width, even after editing cells to trigger recalc.
    – Ryan
    Oct 2, 2023 at 13:23
  • 1
    The issue is that I've locked the cell reference within CELL using $B$2, so where you've copied the formula to a new column it is still looking to cell B2 to determine the width. If you change the $B$2 to B$2 the formula will adjust to whichever column it is in. Oct 2, 2023 at 13:36
  • Oh ok! I changed it to =let( strings,tocol($A2,1), strlen,(cell("width",B2)/2)-1, abbrev,left(strings,strlen)&"…"&right(strings,strlen), if(now(),abbrev,)) and it seems to mostly work and dynamically adjusts if I edit a column width and then edit a cell value to trigger a recalc, although the text still does overflow even with monospace font. I'll accept this as the best answer, though. Thanks!
    – Ryan
    Oct 2, 2023 at 14:46
1

There is no such feature in Google Sheets. What you can do is use a formula in another column to view say the first four and last four characters, like this:

=arrayformula(left(A2:A, 4) & "…" & right(A2:A, 4))

You can then hide the original column. You can still locate the row where a value appears in full by using Edit > Find and replace (Control+Shift+H).

2
  • It's annoying that Google Sheets doesn't offer what I want, but you're right that I could just create a formula in a 2nd column if I'm willing to sacrifice some of the "requirements" I stated. I could even leave the first column visible, and the second column could be something like ="..."&right(A2, 6).
    – Ryan
    Oct 1, 2023 at 23:42
  • Or I could just make the 2nd column right-aligned =A2.
    – Ryan
    Oct 1, 2023 at 23:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.