Find text, remove brackets, and convert to superscript

Question

Level 1

4 points

Find text, remove brackets, and convert to superscript

Apologies if this was not posted in the appropriate Community. There are none for Automator or Applescript and so this one seemed most relevant.

I have a Numbers spreadsheet wherein there are many cells containing text adjacent to other text, delimited by brackets (e.g., abc[123]). I want to create an Automator service, or Applescript app (still pretty new to this, so I am not sure which would be more applicable to my needs), that will find the brackets and text between the brackets (e.g., [123]), remove the brackets (e.g., 123), and convert the text within the brackets to a superscript (e.g.,¹²³), with the final output being "abc¹²³" (and yes, I at least knew how to use the HTML editor to create that superscript with the <sup></sup> tags!).

Any help would be greatly appreciated. Thank you.

MacBook Pro (Retina, 13-inch, Mid 2014), OS X Yosemite (10.10.2)

Posted on Mar 28, 2015 3:37 PM

Reply

Answer 1

Mar 28, 2015 5:19 PM in response to rjpalumbo24

The AppleScript dictionary for Numbers v3.5.2 does not expose cell text to baseline (superscript) features. This can be achieved with GUI scripting by telling the specific Numbers Format ▸ Font ▸ Baseline ▸ SuperScript menu item to do this on the numeric selection. The weakness of GUI scripting is its dependency on Apple not changing the underlying graphical design elements and menu structure in future application releases.

Reply

Answer 2

Pierre L.

Level 5

4,635 points

Mar 29, 2015 12:09 PM in response to VikingOSX

Sorry… the message was for rjpalumbo24.

Reply

Answer 3

Pierre L.

Level 5

4,635 points

Mar 29, 2015 12:06 PM in response to rjpalumbo24

The following script (GUI scripting) should do what you are asking for:

tell application "Numbers"

activate

tell table 1 of sheet 1 of document 1

set theCells to cells whose value ends with "]"

repeat with thisCell in theCells

try

set theValue to value of thisCell

tell me to set P to offsetof "[" intheValue

-- Test the content of the current cell :

1 / P-- error (no left bracket) if P = 0

text (P + 1) through -2 of theValue as integer-- error if [ab] for example

-- Remove the brackets :

set value of thisCell to text 1 through (P - 1) of theValue ¬

& text (P + 1) through -2 of theValue

-- Select the current cell :

set thisName to name of thisCell

set selection range to range (thisName & ":" & thisName)

-- Reformat the digits :

my formatDigits((length of theValue) - P - 1)

end try

end repeat

end tell

tell application "System Events" to keystroke "a" using {shift down, command down}

end tell

on formatDigits(N) -- N = number of digits

tell application "System Events"

delay 0.1

keystrokereturnusing {option down} -- insertion point

delay 0.1

repeat N times

key code 123 using {shift down} -- select the digits

end repeat

delay 0.1

keystroke "+" using {control down, command down} -- superscript

delay 0.1

end tell

end formatDigits

Tested under OS X 10.10.2 “Yosemite” with Numbers 3.5.2.

Reply

Answer 4

rjpalumbo24 Author

Level 1

4 points

Mar 29, 2015 12:31 PM in response to Pierre L.

This looks great! The only problem is that, even when creating a test document named "document 1" with "Sheet 1" renamed to "sheet 1" and "Table 1" renamed to "table 1", the script doesn't know where to find the document (even, if I put it in the iCloud Numbers folder, where it seems to keep looking for it). Also, when trying this with an actual document, the name of the document, the sheets, and tables, all have names comprising multiple words, and when I put those words in place of table 1, sheet 1, and document 1, it keeps giving errors. So I'm not sure if that's a limitation of the script, or if I am missing a character to indicate that words are separate but all part of one name (using an underscore didn't work), and it doesn't seem that simplifying the names would help either, in the former case.

Reply

Answer 5

Pierre L.

Level 5

4,635 points

Mar 29, 2015 1:39 PM in response to rjpalumbo24

No need to rename anything. “Table 1 of sheet 1 of document 1” automatically refers to the table of any new document created at front with Numbers. In order to try the script, just create a new document like the one below, and run the script:

Reply

Answer 6

rjpalumbo24 Author

Level 1

4 points

Mar 31, 2015 12:36 PM in response to Pierre L.

I see. The reason it didn't appear to be doing anything was because it was only working gone 2 cells toward the bottom of the document. Which is now the current problem—it only works on these 2 cells in particular. Selecting a particular cell doesn't do anything, either. So as perhaps an update to the scripts capabilities, is there a way to make the script search every cell that has text in it, and then make the changes (because there are many, many cells that require this modification)? Thank you.

Reply

Answer 7

rjpalumbo24 Author

Level 1

4 points

Mar 31, 2015 12:44 PM in response to Pierre L.

OK. I tested something: I took the one cell that would convert, and just copied and capsized it to many cells in a new document. And the script does in fact change all of them. So that's not the problem—the problem then is that this one particular cell it likes, but it won't modify anything else. Any thoughts?

Reply

Answer 8

Pierre L.

Level 5

4,635 points

Mar 31, 2015 12:56 PM in response to rjpalumbo24

When I read your initial post, I was under the impression that the right bracket was always the last character in the cell, like if the cell contained only abc[123], and not abc[123]efg, for example. I will try to correct the script.

Reply

Answer 9

rjpalumbo24 Author

Level 1

4 points

Mar 31, 2015 12:59 PM in response to Pierre L.

Apologies—I should've known it would have to be that specific. Here is an example:

w[1118]; P{w[+]=pgc[57]}; P{}bam[∆86] ry[506] e[1]/TM6B, Hu Tb[1]

(note: curly brackets are distinct from straight brackets and do not need to be superscripted)

So yes, lots of brackets—and now that you mention it, the one cell that works only has one set of brackets in it, where the close bracket is the final character. So I assume that that is why that one works then.

Reply

Answer 10

Pierre L.

Level 5

4,635 points

Mar 31, 2015 1:00 PM in response to rjpalumbo24

Thanks for the example. That's better than any explanation.

Reply

Answer 11

Pierre L.

Level 5

4,635 points

Mar 31, 2015 5:49 PM in response to rjpalumbo24

The following script should do the trick. It has been tested under OS X 10.10.2 with Numbers 3.5.2 on a Mid 2010 MacBook Pro. If it doesn't work properly on your computer, the first thing to do is to increase the delays. Let me know if it doesn't behave as expected.

tell application "Numbers"

activate

tell table 1 of sheet 1 of document 1

set theCells to cells whose value contains "["

repeat with thisCell in theCells

set thisName to name of thisCell

set selection range to range (thisName & ":" & thisName)

delay 0.2 -- adjust if necessary

tell application "System Events"

keystrokereturnusing {option down}

key code 123 using {command down}

end tell

set P0 to 0

set thisText to value of thisCell

repeat

tell me to set P1 to offsetof "[" inthisText

if P1 = 0 then exit repeat

tell me to set P2 to offsetof "]" inthisText

if P2 = 0 then exit repeat

tell application "System Events"

repeat (P1 - P0 - 1) times

key code 124

end repeat

key code 117

repeat (P2 - P1 - 1) times

key code 124 using {shift down}

end repeat

keystroke "+" using {control down, command down}

delay 0.2

key code 124

key code 117

end tell

set L to length of thisText

if P1 = 1 and P2 = L then

set thisText to text (P1 + 1) through (P2 - 1) of thisText

else if P1 = 1 then

set thisText to text (P1 + 1) through (P2 - 1) of thisText ¬

& text (P2 + 1) through -1 of thisText

else if P2 = L then

set thisText to text 1 through (P1 - 1) of thisText ¬

& text (P1 + 1) through (P2 - 1) of thisText

else

set thisText to text 1 through (P1 - 1) of thisText ¬

& text (P1 + 1) through (P2 - 1) of thisText ¬

& text (P2 + 1) through -1 of thisText

end if

set P0 to P2 - 2

end repeat

delay 0.5 -- adjust if necessary

end repeat

end tell

Reply

Answer 12

Pierre L.

Level 5

4,635 points

Apr 1, 2015 12:19 PM in response to rjpalumbo24

The two modifications below should make the script both more robust and more efficient :

First, replace

if P2 = 0 then exit repeat

with

if P2 < P1 + 2 then exit repeat

Second, replace

delay 0.5 -- adjust if necessary

with

tell application "System Events" to keystrokereturnusing {command down}

repeat until formatted value of thisCell = thisText

end repeat

Reply

Answer 13

rjpalumbo24 Author

Level 1

4 points

Apr 1, 2015 12:27 PM in response to Pierre L.

Wow, thank you for all of the work you've put into this. Two things:

In the version without the two modifications, some cells will be entirely erased of their contents.

In the version with the modifications, it stalls after about 2 cells.

Reply

Answer 14

Pierre L.

Level 5

4,635 points

Apr 1, 2015 12:36 PM in response to rjpalumbo24

I have tested the script a great number of times with a lot of different formulas, and it seems to work flawlessly, at least in a new blank document using the default settings. I suggest that you first temporarily increase the two delays from 0.2 s to 0.5 s for example, just to be sure the issue doesn't come from these two delays. Then maybe you could post the content of the problematic cells.

Reply

Answer 15

Pierre L.

Level 5

4,635 points

Apr 1, 2015 12:44 PM in response to rjpalumbo24

I think I found out what is going wrong. In its present state, the script doesn't work with formulas whose text is wrapping in a cell. I had always tested the script with very large columns.

Reply