+ Reply to Thread
Results 1 to 9 of 9

problem with converting numbers to TEXT format and applying comma style

  1. #1
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    problem with converting numbers to TEXT format and applying comma style

    Hi all,
    I have a column "U" with numbers. I had to change the format of cells from NUMBER to TEXT (because the next step is moving the data to Word) and apply comma style.

    I managed to change the format to TEXT and then apply the comma style by using this code:


    Please Login or Register  to view this content.
    but...
    Excel recognizes writing numbers as text as a mistake. I ignored this mistake and it looks fine, but it still cannot be imported to Word (all values are displayed as 0).

    The entry now is justified (aligned) to left, while other columns are aligned to right and are displaying properly in Word.

    How to convert it to `normal`TEXT format that will appear in a Word file? Thanks in advance for your help.

  2. #2
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,656

    Re: problem with converting numbers to TEXT format and applying comma style

    try adding single quote so excel treats as text.

    Please Login or Register  to view this content.
    Cheers
    Andy
    www.andypope.info

  3. #3
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: problem with converting numbers to TEXT format and applying comma style

    Andy excel already treat these cells as text, but Word still cannot read them.
    I applied your line to my code, but nothing changed.

  4. #4
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,656

    Re: problem with converting numbers to TEXT format and applying comma style

    How exactly are the values getting into word? When I copied and pasted the number formatting was maintained.

  5. #5
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: problem with converting numbers to TEXT format and applying comma style

    it is in envelope form (mailings), I create an envelope in which the data from several columns is imported.

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,656

    Re: problem with converting numbers to TEXT format and applying comma style

    Works for me using mailmerge with list coming from excel file.

    can you post example workbook

  7. #7
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: problem with converting numbers to TEXT format and applying comma style

    here you are, column U
    plus: when I apply the code we discussed above, several other columns change the formatting and appears as a 12:00:00 AM value instead of values from cells. (for example: J, K, L)
    Attached Files Attached Files
    Last edited by Vogelmann; 10-23-2012 at 08:28 AM.

  8. #8
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    19,656

    Re: problem with converting numbers to TEXT format and applying comma style

    If I copy the values in your example, column U, and change them to non text. I get the following

    1282.5 - Real Number
    1.282,50 - formatted value

    I not seeing the problem you are. Number format in word is correct when cell is formatted as value.

    You could try adding an additional field, =TEXT(AA2,"#,##0.00")
    change AA2 to be the cell with the original numeric value

  9. #9
    Registered User
    Join Date
    07-01-2012
    Location
    Tilburg, the Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    96

    Re: problem with converting numbers to TEXT format and applying comma style

    ok I managed to deal with that problem by using this code:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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