I need help with regular expressions and formatting

I am using this Extract matching text column to clean up cell number formats but it’s not quite doing the trick. This format +27 (73) 899-2999 should be formatted like this 738992999
No matter what numbers and characters they enter, i really just need the last nine numbers from their entry.
Any ideas? @Darren_Murphy or anyone else who could help?

I’m looking for same thing… RegEx to clean phone number :pray:

I started having a look at this yesterday, and ended up going down a bit of a rabbit hole, so I put it aside.

Had another look just now and it occurred to me that all you really need to do is strip out all the non-digits and then return the last 9 characters of whatever is remaining. So with that in mind, here is a bit of rough and ready JavaScript that does that. I’m sure it could be improved.

const parts = p1.split('');
let digits = [];
while (parts.length > 0) {
  let bit = parts.shift();
  if (bit.match(/\d/)) {
    digits.push(bit);
  }
}
return digits.slice(-9).join('');

3 Likes

Much obliged :face_with_monocle:

Genius, this works like a charm.
I am seeing this yellow banner, have i set it up correctly? It’s working fine even with the Error banner. Any ideas?

Do you have any rows that don’t have a value in the Phone Number column?
Empty rows would cause that error. You could add a return statement in the code to avoid that, but it isn’t really necessary.

Yes i have blank rows. The cell number is an optional field.
Besides that it’s all working fine. I’ll test for a while and see, seems all good though👍

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.