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

 

Reply


Terms & Conditions