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

Question

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

Reply

Answer 1

Top-ranking reply

Badunit

Level 7

28,636 points

Nov 2, 2023 11:05 AM in response to gestyle

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.

Reply

Answer 2

gestyle Author

Level 1

1 points

Nov 2, 2023 12:01 PM in response to Badunit

Thanks a lot for your message, Badunit

Is there perhaps any dependency with the source-cell-format, which in automatic mode turns into a date ?

For that reason, instead of a «text-field» or an «automated» Data Format, I attempted to create a custom-digits-format, which currently still seems to bug.. :P

( 2023-11-02_TABLE-EXCERPT_REPLACE_FROM_RIGHT_OR_LAST_OCCURRENCE-ISSUE.53.2.png )

( 2023-11-02_TABLE-EXCERPT_REPLACE_FROM_RIGHT_OR_LAST_OCCURRENCE-ISSUE.53.3.png )

But even when selected, it doesn’t lock and remains a «text» … Not sure why..

( 2023-11-02_TABLE-EXCERPT_REPLACE_FROM_RIGHT_OR_LAST_OCCURRENCE-ISSUE.53.4.png )

So, it seems to currently affect both formulas. Do you perhaps see what’s happening ?

:)

Reply

Answer 3

Recycleur

Level 4

1,609 points

Nov 2, 2023 1:13 PM in response to gestyle

The file is set to English so you have to use a period (.) and not a comma (,).

=IFERROR(SUBSTITUTE(B12, ":",".",COUNTMATCHES(B12,":")),B12)

Now to get the format you want use Duration with Style 0:00:00.000 (Picture in French but I think you see where to go).

Reply

Answer 4

gestyle Author

Level 1

1 points

Nov 3, 2023 9:35 AM in response to Recycleur

Merci également to you, Recycleur,

I finally got rid of a character-issue mentioned by Badunit. I think this screenshot makes things clear for whom might ever run into the same issues :

Thanks again to both of you !

:)

Reply

Answer 5

Recycleur

Level 4

1,609 points

Nov 3, 2023 10:33 AM in response to gestyle

gestyle, be mindful of the illusions. Values are not always what they appear to be. Comma and decimal point are not always interchangeable depending on the language. Even divising the value by 1 is hit-and-miss, I dropped it for that reason.

The small table Décimal provides the valid decimal separator in all circumstances. Value 1.0 in A1, MID(A1,2,1) in A2.

Reply

Answer 6

gestyle Author

Level 1

1 points

Nov 6, 2023 4:41 PM in response to gestyle

I came across another issue among the values of my chart : some values got interpreted 00:24:18,00 instead of 00:00:24,18. While others get correctly interpreted : start-value : 0:17:73 … Correct result : 0:17,73

I’m wondering if there is any trick able to fix the issue with those longer strings ending with 00 –?–

( 2023-11-07_TABLE-EXCERPT_REPLACE_FROM_RIGHT_OR_LAST_OCCURRENCE-ISSUE.53.1.png )

Reply

Answer 7

gestyle Author

Level 1

1 points

Nov 6, 2023 7:52 PM in response to Recycleur

Hello and thank you for your reply, Recycleur !

If there were a way to swap the 00 to the front, the durations would get correctly interpreted and displayed.

The line 00:24:18,00 is only faked at this time. You’re right.

(Yes, I was wondering about one missing post :) )

Reply

Answer 8

Yellowbox

Level 7

21,331 points

Nov 7, 2023 2:12 AM in response to gestyle

Hi gestyle,

Back to the title of your original post:

gestyle wrote: How to replace / substitute THE LAST OCCURRENCE of a character in a string

The functions TEXTBEFORE and TEXTAFTER are your friends.

-1 will find the last occurrence of ":"

&","& will insert a comma. Assuming that your Language & Region is France, that will represent a decimal separator. But the result will be Text, not minutes:seconds:milliseconds.

It will represent minutes, seconds and decimal fractions of seconds.

Good luck with your project.

Regards,

Ian.

Reply

Answer 9

Recycleur

Level 4

1,609 points

Nov 7, 2023 3:15 AM in response to gestyle

This will ignore what is after the two characters following the last colon.

=TEXTBEFORE("0:"&$B47,":",−1)÷1+DURATION(,,,,LEFT(TEXTAFTER("0:"&$B47,":",−1),2)÷100)

Ian, the reason I use TEXTBEFORE("0:"&$B47, etc. is that without the "0:"& part a value like 23:45 would only return 23, which creates an error when trying to convert it to a duration. It might be overkill in this case if data is guaranteed to always have at least three parts but I don't take any chance (also I write my answers thinking of all future readers who might have a similar but not quite identical situation).

Interesting tidbit I discovered while doing this project. A value like 2:14:6:3:5 is the same as 2 weeks 14 days 6 hours 3 minutes 5 seconds, a shorthand for function DURATION. Like =B45+"1:6:27:0". It might be useful sometimes.

Reply

Answer 10

gestyle Author

Level 1

1 points

Nov 7, 2023 4:06 AM in response to Recycleur

Thanks both of you for your inputs, Recycleur, Ian !

It might take a while for me to chew all this information :)

Please allow me to insert this basic question :

(Here I use the graphic-UserInterface of NUMBERS, just for convenience / layout purposes)

Can you show the entire formula instead of the dots, please ?

Reply

Answer 11

Recycleur

Level 4

1,609 points

Nov 7, 2023 7:06 AM in response to gestyle

They are not dots, they are the commas separating the missing parameters (equivalent to 0 in this function, to the default parameter in other functions).

This is 30 seconds. You don't need the commas AFTER the last value.

Reply

Answer 12

gestyle Author

Level 1

1 points

Nov 9, 2023 6:33 PM in response to Recycleur

Thanks again to you @Recycleur,

I made a tiny syntax-adaptation for the formula to be valid in my QWERTZ machine/environment – I think this is important for others to stress – see screenshot.

Now while the formula gives a coherent output on short strings (the 3-blocks-strings), it’s still kind of swaping the duration-units when processing long strings (the 4-blocks-strings) :

Do you see any way how to also fix this point ?

:)

Reply

Answer 13

Badunit

Level 7

28,636 points

Nov 2, 2023 12:59 PM in response to gestyle

My source column was pre-formatted as text. The custom format in your screenshot does not match what I see in column B. It has no colons in it, all the numbers run together. I tried a custom date format similar to yours, except with colons between the numbers, and it worked correctly. But I recommend formatting column B as text.

Reply

Answer 14

Recycleur

Level 4

1,609 points

Nov 6, 2023 6:24 PM in response to gestyle

I don't understand the issue. If 0:41:48 is OK to turn into 0:41.48, why is 00:44:48:00 not OK to turn into 0:44:48.00 ? I thought the last two digits after the last colon were always the 100th of a second.

Note that in the top table, you have 00:24:18,00 (with a comma) in the source column while in the bottom table that's not the case. How can the 00 after the comma be understood to go in front? Is it a CSV reading error?

I worked out another formula, not better than the original except that it ignores the comma/point dilemma for the decimal separator.

TEXTBEFORE("0:"&$B38,":",−1)÷1+DURATION(,,,,TEXTAFTER("0:"&$B38,":",−1)÷100)

Oh, and the division by 1 somehow converts a string into a number. You must have seen it in my first answer, which I edited afterwards to remove it. But sometimes it works.

Reply

Answer 15

Recycleur

Level 4

1,609 points

Nov 10, 2023 6:39 AM in response to gestyle

If the valid times ALWAYS have three parts then we can dispense with some extra care and assume that the fourth part is always useless.

with ; as separator

TEXTBEFORE($B47;":";2)÷1+DURATION(;;;;LEFT(TEXTAFTER($B47;":";2);2)÷100)

or

with , as separator

TEXTBEFORE($B47,":",2)÷1+DURATION(,,,,LEFT(TEXTAFTER($B47,":",2),2)÷100)

Reply