Help

The Community will be temporarily unavailable starting on Friday February 28. We’ll be back as soon as we can! To learn more, check out our Announcements blog post.

Mobile Number extraction

Topic Labels: Formulas
907 3
cancel
Showing results for 
Search instead for 
Did you mean: 
conbadenhorst
4 - Data Explorer
4 - Data Explorer

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!
3 Replies 3

Try this: 

Screenshot 2024-05-14 at 11.34.55 PM.png

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!

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

Screenshot 2024-05-15 at 3.49.07 PM.png

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