+ Reply to Thread
Results 1 to 5 of 5

Using Concatenate with specialty formatted numbers

  1. #1
    Registered User
    Join Date
    03-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    33

    Using Concatenate with specialty formatted numbers

    Column A = area code
    Column B = first three digits
    Column C = last four digits

    Columns A and B never begin with a zero, so non-issue
    Column C wouldn't display the fourth digit if there were leading zeros, so the formatting of the cells was changed to force four digits and prepend with 0's if needed

    Problem: When attempting to concatenate (A1,B1,C1), the zeros would not populate...

    My solution (as there were only 80ish of them) was to manually key in the single ' with the zeros - so 0001 was entered as '0001

    Curious though, if this were to scale substantially larger, is there a formula that could push that single quote through a larger data set?

    My sample data set is attached...
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: Using Concatenate with specialty formatted numbers

    Maybe try =CONCATENATE(A1,B1,TEXT(C1,"#0000"))

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using Concatenate with specialty formatted numbers

    If these are phone numbers maybe you want the hyphens in there also:

    =A1&"-"&B1&"-"&TEXT(C1,"0000")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Registered User
    Join Date
    03-31-2013
    Location
    USA
    MS-Off Ver
    Excel 2013
    Posts
    33

    Re: Using Concatenate with specialty formatted numbers

    JieJenn had it...Tks so much.

    Longer explanation, the company I work for has an upload form for our clients to import users, drivers, etc., and the upload utility craps the bed if there are dashes in there.

    My customer sent my colleague the list with normal phone numbers (with dashes), so he did a text to column conversion, then changed column C to Special formatted, thinking a regular concatenation would work, which obviously didn't...

    I looked at it, saw the limited number of values in column C with leading zeros so manually keyed them in (less than five minutes), and just sent it back.

    Afterward, I figured the solution I provided really wouldn't scale if another customer with a larger fleet did the same, so started searching through excel for the format to do this more quickly and turned here when nothing obvious stood out.

    Of course they think I'm a genius for figuring it out and I said, Dude, it's called excelforum.com. Google it you might be able to use it too in the future! His response:

    How much is it?

    Me: It's free...

    Him: To register or ask questions?

    Me: Both...

    Him: For how long?

    Me: Forever...

    Him: Cool...

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Using Concatenate with specialty formatted numbers

    Good deal. Thanks for the feedback!

+ 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] Formatted cells used in a Concatenate formula
    By joebell in forum Excel General
    Replies: 5
    Last Post: 10-11-2014, 06:39 AM
  2. Replies: 8
    Last Post: 05-03-2014, 12:49 AM
  3. [SOLVED] concatenate formatted cells in excel
    By Sandwiches2 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-14-2006, 08:35 PM
  4. [SOLVED] how do i concatenate conditionally formatted cells in excel
    By Sandwiches2 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 03-13-2006, 09:45 PM
  5. [SOLVED] tracking customer orders for ad specialty distributror
    By Abie Ross in forum Excel - New Users/Basics
    Replies: 4
    Last Post: 09-23-2005, 01:05 PM

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