+ Reply to Thread
Results 1 to 5 of 5

Formatting using the &. Please help!

  1. #1
    Registered User
    Join Date
    03-30-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    50

    Formatting using the &. Please help!

    Good afternoon.

    Hoping someone can help with this issue:

    I have a simple spreadsheet with three cells next to one another. Assume they're A1, B1, and C1.
    A1 contains a date.
    B1 contains a dollar amount.
    C1 contains a text string.

    At work, I make payment arrangements and have to copy and paste this information into a third party software. When I do this, I get really ugly spacing. Something like:
    5/16__________$100.00__________to acct 1234567890

    Deleting the large spaces manually is too time consuming. So I tried putting all the info into one cell like this: =A1&" "&B1&" "&C1
    This solved the issue of the spacing, but I lose the formatting. The result looks like:
    "42506 100 to 1234567890"

    How do I get all three pieces of information into one cell with the formatting (date & dollar amt) and without the huge spaces between them? I'm trying to create this for users who are... *ahem* "Unsophisticated" when it comes to Excel. They need to be able to plug in data, copy, paste and move on.

    Thank you for taking the time to read this and I look forward to your thoughtful replies.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,486

    Re: Formatting using the &. Please help!

    Use TEXT. For example,
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    and
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert ben_hensel's Avatar
    Join Date
    01-26-2012
    Location
    Northeast USA
    MS-Off Ver
    Office 365
    Posts
    2,043

    Re: Formatting using the &. Please help!

    Use the TEXT function to force a number into an explicitly-formatted text string that you can concatenate together.

    Please Login or Register  to view this content.
    EDIT:
    tch, TMS ninja'd me.
    Click the [* Add Reputation] Button to thank people for help!
    Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
    Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.

  4. #4
    Registered User
    Join Date
    03-30-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    50

    Re: Formatting using the &. Please help!

    Thank you to both of you guys. I really appreciate it.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,486

    Re: Formatting using the &. Please help!

    You're welcome.

    If the Account Number can begin with zeroes, you might also need to use:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    @ben_hensel: it happens



    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

+ 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. Replies: 9
    Last Post: 03-07-2016, 10:39 AM
  2. Formatting Cells with Date or Text Values in a Conditional Formatting Formula
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 09:36 AM
  3. Replies: 1
    Last Post: 08-28-2013, 09:07 AM
  4. Replies: 1
    Last Post: 07-02-2013, 07:57 AM
  5. Delete Conditional Formatting conditions but keep cell formatting - Excel 2010
    By tetreama in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-09-2012, 08:28 PM
  6. [SOLVED] Userform TextBox Problem... formatting code clears data instead of formatting
    By michaeljoeyeager in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-26-2012, 11:00 AM
  7. Pivot Table graph looses formatting after running formatting macro
    By arthurbr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-27-2011, 04:16 AM

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