Skip to main content

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!

Try this: 

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

Link to base


Try this: 

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

Link to base


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!


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!


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" ) )