+ Reply to Thread
Results 1 to 9 of 9

Changing data input number from (123) 456-7890 to 123-456-7890

  1. 10-17-2017, 11:14 AM #1
    Explorer Group
    Explorer Group is offline
    Registered User
    Join Date
    10-17-2017
    Location
    CANADA
    MS-Off Ver
    OFFICE 365
    Posts
    2

    Angry Changing data input number from (123) 456-7890 to 123-456-7890

    I am organizing an input data sheet that contains thousands and thousands of entries. To make the data much more appealing to the eye, I am attempting to change the already inputted data from (123) 456-7890 to 123-456-7890. Most of the entries are already in this format. I have looked at multiple posts on here before I made an account to ask myself.

    I have tried:

    - changing the formula (I may of just put the wrong formula but nothing happens to the selected column at all)
    - changing from general to phone number


    Please help me

    Diane.
    Reply With QuoteRegister To Reply
  2. 10-17-2017, 11:17 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,255

    Re: Changing data input number from (123) 456-7890 to 123-456-7890

    Two sweeps of find and replace:

    1. Find ( and replace with a blank.
    2. Find ) and a space and replace with a -.

    Or, in a fresh column:

    =SUBSTITUTE(SUBSTITUTE(A1,") ","-"),"(","")
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 10-17-2017, 11:18 AM #3
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    30,948

    Re: Changing data input number from (123) 456-7890 to 123-456-7890

    Try
    data in A1

    in B1

    =SUBSTITUTE(SUBSTITUTE(A1,"(",""),") ","-")

    Ali's solution much better!
    Reply With QuoteRegister To Reply
  4. 10-17-2017, 11:19 AM #4
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2406
    Posts
    44,662

    Re: Changing data input number from (123) 456-7890 to 123-456-7890

    Try this, copied down:
    =IF(LEFT(A2,1)="(",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),"(",""),")","")," ","-"),A2)

    assumes data start in A2...
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
    Reply With QuoteRegister To Reply
  5. 10-17-2017, 11:49 AM #5
    mstwntd
    mstwntd is offline
    Registered User
    Join Date
    06-11-2010
    Location
    USA
    MS-Off Ver
    Excel 2003
    Posts
    43

    Re: Changing data input number from (123) 456-7890 to 123-456-7890

    If you remove all the extra characters you can change the format to phone number. Excel won't see it as a phone # with all the extra. I prefer to keep the #s clean. This helps me match to other sources if needed. The extra characters makes Excel think it is a text type.

    I hope this helps.
    Reply With QuoteRegister To Reply
  6. 10-17-2017, 11:56 AM #6
    Explorer Group
    Explorer Group is offline
    Registered User
    Join Date
    10-17-2017
    Location
    CANADA
    MS-Off Ver
    OFFICE 365
    Posts
    2

    Re: Changing data input number from (123) 456-7890 to 123-456-7890

    Thank you so so so so very much. Made for less time wasted!

    Diane
    Reply With QuoteRegister To Reply
  7. 10-17-2017, 11:59 AM #7
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Changing data input number from (123) 456-7890 to 123-456-7890

    Or this
    ***Array formula
    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
    You will know the array is active when you see curly braces { } appear around your formula.
    If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    v A B
    1 (123) 456-7890 123-456-7890
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  8. 10-17-2017, 12:11 PM #8
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2505 (Windows 11 Home 24H2 64-bit)
    Posts
    91,255

    Re: Changing data input number from (123) 456-7890 to 123-456-7890

    Thanks for the rep!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
  9. 10-17-2017, 12:15 PM #9
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Changing data input number from (123) 456-7890 to 123-456-7890

    Here is one more
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    Or
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Last edited by AlKey; 10-17-2017 at 12:37 PM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Changing the order of the input data for CHARTS without changing the order in the chart
    By lukelucky in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-17-2015, 04:36 AM
  2. Changing value of a cell based on number input in another
    By mrmatt81 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2015, 11:53 AM
  3. Data Input Form with number of line items based on user input
    By j_gideon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-25-2013, 02:54 PM
  4. formula: data in output cell is not changing when data in input cells are modified
    By Kuttisankaran in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-07-2013, 05:58 AM
  5. [SOLVED] Changing number of input boxes???
    By srroduin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-14-2006, 10:50 AM
  6. [SOLVED] Insert "-" in text "1234567890" to have a output like this"123-456-7890"
    By Alwyn in forum Excel General
    Replies: 3
    Last Post: 10-25-2005, 07:05 PM
  7. insert a space in a phone #. change (123)456-7890 to (123) 456-78
    By leo in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-14-2005, 10:07 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1