You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

gestyle Author

Level 1

1 points

How to replace / substitute THE LAST OCCURRENCE of a character in a string ?

Hello,

I’m in NUMBERS 13.1 on MacOS 12.6.6 (but it’s not a version issue).

I have several values with different lengths in column B, e. g.: B6 : 56:01:59

And I want to REPLACE or SUBSTITUTE THE LAST OCCURRENCE of ":" by "," in order to return the following value instead in C6 : 56:01,59

i. e. : having the last digits after the comma identified as milliseconds.

I tested different formulas in column C. e.g. : C6 : REPLACE(B6;”:";",";2)

It’s returning 56:01,59 which is correct, but wouldn’t work with a longer string ( WRONG 00:00,00:00 ).

Select-Replacing the FIRST OCCURRENCE (start counting from LEFT) seems easier than Select-Replacing THE LAST OCCURRENCE of the ":".

How can I Select-Replace THE LAST OCCURENCE of ":" in a formula for cells with different string-lengths ? and

How would a nested Formula look like, using REPLACE/SUBSTITUTE and RIGHT, in oder to invert the count-direction ?

2023-11-02_TABLE-EXCERPT_REPLACE_FROM_RIGHT_OR_LAST_OCCURRENCE-ISSUE.53.1.png

/ How does the NUMBERS formula look like, assuming  working with either : 

a) a « REPLACE Last Occurrence Of ”:” » approach,  or 

b) a « REPLACE First Occurrence from RIGHT » approach

Any help much appreciated

Posted on Nov 2, 2023 9:54 AM

Top-ranking reply

Badunit

Level 7

28,702 points

Posted on Nov 2, 2023 11:05 AM

If all the source string is guaranteed to have at least one ":" and if that ":" will always be followed by exactly 2 additional characters (as in 6:21 or 6:21:32 but not 6:213 or 6213),

=REPLACE(B2,LEN(B2)−2,1,",")

Otherwise, this one will replace the last ":" no matter where it is in the string

=IFERROR(SUBSTITUTE(B2,":",",",COUNTMATCHES(B2,":")),B2)

The IFERROR function is there is for when the string does not have a ":". There is probably a better formula but these two appear to do what you requested.

View in context

18 replies