Hi, Good day ! Is there any regex formula to remove second '0' on mobile number ? Below is the example, I want to remove first '0' after '+30 ' Mobile NumberOutput+30 0123304011+30 123304011+30 146321938+30 146321938+30 0440324037+30 440324037+30 019663515+30 19663515+30 172881533+30 172881533+30 178853653+30 178853653+30 12343420+30 12343420+30 1325750900+30 1325750900 The desired output is to show '+30' following with 1 space and other numbers ( must start with non-zero ). Second question, what if there is also some numbers has few 'spaces' after '+30' For example : +30 01234069 (contain 2 spaces ) +30 011298799 ( contain 3 spaces ) Thanks !

Regex_Replace to remove specific number in middle of string

Hi,

Good day !

Is there any regex formula to remove second '0' on mobile number ?
Below is the example, I want to remove first '0' after '+30 '

Mobile Number	Output
+30 0123304011	+30 123304011
+30 146321938	+30 146321938
+30 0440324037	+30 440324037
+30 019663515	+30 19663515
+30 172881533	+30 172881533
+30 178853653	+30 178853653
+30 12343420	+30 12343420
+30 1325750900	+30 1325750900

The desired output is to show '+30' following with 1 space and other numbers ( must start with non-zero ).

Second question, what if there is also some numbers has few 'spaces' after '+30'
For example :
+30 01234069 (contain 2 spaces )
+30 011298799 ( contain 3 spaces )

Thanks !

Regex

Accepted answers

Yoshiro_Fujimori

@fikrizahari ,

How about this formula?

REGEX_Replace([Mobile Number], "(\+30)\s+0?(.*)", "$1 $2")

Output:

The last row is the case for mutilple spaces.

RegEx.yxmd

All comments

IanMacLeod

You could use Text to Columns on space delimiter to give [Output1] and [Output2] columns.

Followed by Formula Tool: IF LEFT([Output1],1) = "0" THEN ""

Then a second formula to concatenate [Output1] & [Output2]: [Output1] + " " + [Output2].

Yoshiro_Fujimori

@fikrizahari ,

How about this formula?

REGEX_Replace([Mobile Number], "(\+30)\s+0?(.*)", "$1 $2")

Output:

The last row is the case for mutilple spaces.

RegEx.yxmd

Qiu

@fikrizahari
If we dont insist on Reg_Replace😁

0314-fikrizahari.yxmd

fikrizahari

Hi @Yoshiro_Fujimori ,

It works like a charm !

However, I would like to understand the meaning of each expressions.

(\+30) : find require string

\s+ : multiple whitespaces

0 : find 'zero'

? : im not sure

(.*) : any following arbitrary string

$1 $2 : im not sure

Please correct me if im wrong above.
Thanks !

Yoshiro_Fujimori

@fikrizahari ,

I am not an expert of RegEx, so I would recommend check for books or websites for RegEx.

In this particular case, the trick is as follows:

The pattern between () can be called later by $n.

So (\+30) can be called by "$1", and (.*) can be caclled by "$2".

"+" needs to be escaped with "\", as it has a special meaning in RegEx.

"0?" matches with the pattern of 0 or 1 time occurrence of "0".

So the idea is

Find the pattern of

String "+30" followed by one or more spaces, (optionally) followed by "0", and the rest of the string.

And return the above red strings, with one space in between.

I hope this explanation makes some sense.

fikrizahari

@Yoshiro_Fujimori

Sound clear to me.
Thanks a lot !

Hi @Qiu and @IanMacLeod,
Both answers also shown similar output.
It's also really helpful without require any regex formula.

Thank you as well !

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703