Re: Extracting numbers from a single sell

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Mon, 29 Dec 2008 07:18:18 -0800 (PST), Georgi <inter778@xxxxxxxxx> wrote:

Hi Ron,

I didn`t saw this part of your post. I did it and it is working
briliantly! Sorry for the waste of your time !

P.S. Now after I`ve asked they`ve sent me a real table with around
1000 cells, so I can see if there is any variantions in the mobile
numbers.... and I found that the personal that entered the phone
numbers, used all kind of ways to enter them. In some cases they used
0877/55/44/33 ; 0877-55-44-33 - I`ve used Find&Replace on them and
after that your formula worked just perfect. But there is another case
- 0877 554433 or 0877 55 44 33 - in this case I don`t think that there
is something that we can do, despite some handwork :). Now I`m trying
to find a way to just find the cells that contain numbers with 3 to 6
digits, so I can manualy check what is the problem with them, but I
cannot find the right way to do it - I`ve tried some strings in
Find&Replace but they don`t seem to work.

Instead of using the Find/Replace, try using the following Regex instead:

"\b\d[-\d\s\/]{3,}\d\b"

So your formula might be:

=RegexMid($U2,"\b\d[-\d\s\/]{3,}\d\b",COLUMNS($A:A))

It may be that this may return data that is not valid, but that may be easier
to deal with. In particular, this regex is looking for a string that
1. Start with a digit
2. The next three or more characters can be a hyphen, digit, space or
backslash.
3. End with a digit.

So it should pick up all five digit string, even if they include the extra
characters. But it could also pick up something like 1///2. That's probably
not a problem for your database. (If it is, we could deal with it by making
the regex more complex, but I'm lazy :-))

--ron
.

Relevant Pages

  • Re: Regex in java
    ... string data that is actually a BCD number. ... Seems to me regex can skip over white space. ... So I think it needs either one digit before the. ...
    (comp.lang.java.programmer)
  • Re: Use parentheses in translation strings
    ... I want to replace the 4 digit ... You say you're doing this "for grouping in string matching later". ... single characters for other characters, ... To unsubscribe, e-mail: beginners-unsubscribe@xxxxxxxx ...
    (perl.beginners)
  • Re: VBA Code for converting Type-E UPC to Type-A (barcodes)
    ... Function IsNumber(ByVal Value As String) As Boolean ... ' Get local setting for decimal point ... following code input the result of a Type-A UPC into B5 & the skip digit into ... UPCEString$ = UPCE ...
    (microsoft.public.excel.programming)
  • Re: RegExp irregularity in JScript
    ... > characters, containing at least digit, one lower case and one upper case ... of characters (i.e. digits, lower case, upper case) in the string is at least 4 ...
    (microsoft.public.scripting.jscript)
  • Re: writing get_script as an external routine callable by C
    ... I _STRONGLY_ suggest to attend a Perl class or a self-study course where ... If you mean "begins with a digit" as you said above ... Case 2 matches if the beginning of the string is followed by 1 or more ... If you put the two Bushs together in their over seven years of their two ...
    (comp.lang.perl.misc)