Mobile Number extraction

[conbadenhorst]

Hi, 

I am trying to clean up info from online forms we receive from another company. I need to extract the mobile numbers so that I can automate the contact process as CRM.

Anyway, I have a reference column with the name "message". In there is text as follows: Gender: male
Firstname: Brian
Lastname: Dawson
Email: brian@valuesystems.com.au
Street: Albert Dr
Number: 73
ZIP: 2071
City: Sydney
Country: AU
Phonenumber: +61418212496

OR AS FOLLOWS:

Street: Ponderosa parade
Number: 8/13
ZIP: 2102
City: Warriewood
Country: AU
Phonenumber: 0450294193

All i want to do is generate a result of a standard australian mobile number to look as follows: All australian mobile numbers are 10 digits... So i would need to convert the +61 prefix to 0 as well...

0450294193

Please help... I have tried this all day and get close, but not reliably so! 

Current formula: 

(MID(message,FIND("04",message,1),10))

Thanks!

[TheTimeSavingCo]

Try this: 

SUBSTITUTE(
  SUBSTITUTE(
    Notes,
    LEFT(
      Notes,
      FIND(
        "Phonenumber",
        Notes
      ) + 12
    ),
    ""
  ),
  "+61",
  "0"
)

Link to base

[conbadenhorst]

Thank you for your help!! 

Your formula is close! Some fields don't work 100% yet..

Sample text:

SOFORTANGEBOT Felder-Group Hammer Bandsaw N2-35

----------------
Gender: male
Firstname: Ian
Lastname: Rudd
Email: idrudd@optusnet.com.au
Street: Turiell Bay Road
Number: 2b
ZIP: 2229
City: Lilli Pilli
Country: AU
Phonenumber: 0408621538
Company: Shire Woodworking Club
Type Of Business: OTHER

RESULT: on some lines, more text stays instead of it only being a number..

0408621538 Company: Shire Woodworking Club Type Of Business: OTHER

Thanks again for your input!

[TheTimeSavingCo]

No worries!  I've added some functionality to handle that new type of text:

IF(
	LEFT(
	  SUBSTITUTE(
	    SUBSTITUTE(
	      Notes,
	      LEFT(
	        Notes,
	        FIND(
	          "Phonenumber",
	          Notes
	        ) + 12
	      ),
	      ""
	    ),
	    "+61",
	    "0"
	  ),
	  FIND(
	    '\n',
	    SUBSTITUTE(
	      SUBSTITUTE(
	        Notes,
	        LEFT(
	          Notes,
	          FIND(
	            "Phonenumber",
	            Notes
	          ) + 12
	        ),
	        ""
	      ),
	      "+61",
	      "0"
	    )
	  ) - 1
	),
	LEFT(
	  SUBSTITUTE(
	    SUBSTITUTE(
	      Notes,
	      LEFT(
	        Notes,
	        FIND(
	          "Phonenumber",
	          Notes
	        ) + 12
	      ),
	      ""
	    ),
	    "+61",
	    "0"
	  ),
	  FIND(
	    '\n',
	    SUBSTITUTE(
	      SUBSTITUTE(
	        Notes,
	        LEFT(
	          Notes,
	          FIND(
	            "Phonenumber",
	            Notes
	          ) + 12
	        ),
	        ""
	      ),
	      "+61",
	      "0"
	    )
	  ) - 1
	),
	SUBSTITUTE(
	  SUBSTITUTE(
	    Notes,
	    LEFT(
	      Notes,
	      FIND(
	        "Phonenumber",
	        Notes
	      ) + 12
	    ),
	    ""
	  ),
	  "+61",
	  "0"
	)
)