Regex_Replace to remove specific number in middle of string

[fikrizahari]

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 !

[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 · Accepted solution]

@fikrizahari ,

How about this formula?

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

Output:

The last row is the case for mutilple spaces.

[Qiu]

@fikrizahari 
If we dont insist on Reg_Replace😁

[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 !