+ Reply to Thread
Results 1 to 3 of 3

transform us & uk numbers in mention format

  1. #1
    Registered User
    Join Date
    07-28-2020
    Location
    Melbourne,Australia
    MS-Off Ver
    365
    Posts
    20

    transform us & uk numbers in mention format

    Hi all,

    I am making a database for WhatsApp marketing. I need the data in particular format. Data is mobile number. Below I mentioned how the final format look like.

    USA:+1-541-754-3010
    UK:+44 7911 123456

    Remove all other characters if present like 0,(0),0044

    Attach the file by name "Test2-Check" with two tabs of usa & uk.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-09-2014
    Location
    Newcastle, England
    MS-Off Ver
    2003 & 2013
    Posts
    1,986

    Re: transform us & uk numbers in mention format

    Not being sure how USA numbers work I've assumed they should always be 10digits (without the country code). I then manually did find replace to get rid of all hyphens (-), full stops (.), brackets and spaces, then add the following to B2 and drag down:
    Please Login or Register  to view this content.
    A number of rows will return false as they look really dodgy!
    Last edited by pjwhitfield; 10-08-2020 at 05:43 AM.
    If someone has helped you then please add to their Reputation

  3. #3
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: transform us & uk numbers in mention format

    Hi

    Try this approach with a helper column to facilitate
    Remove all non numeric digits and (0): in B2 use
    For USA B2 and C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    For UK B2 and C2
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    See the file
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Transform text to numbers
    By leovilla in forum Excel General
    Replies: 3
    Last Post: 08-07-2020, 08:57 AM
  2. Transform to readable format
    By tiger19 in forum Excel General
    Replies: 6
    Last Post: 04-09-2019, 10:12 AM
  3. Replies: 4
    Last Post: 04-02-2015, 09:01 AM
  4. VBA to Transform and format data
    By rggovani in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-14-2013, 09:18 AM
  5. transform text to a DD:HH:MM:SS format
    By akasha303 in forum Excel General
    Replies: 4
    Last Post: 10-19-2011, 09:31 AM
  6. Transform matrix into a row of numbers
    By studentin_in_not in forum Excel General
    Replies: 1
    Last Post: 09-13-2010, 09:28 AM
  7. Transform the format of data
    By caldera55 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-17-2008, 01:20 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1