Make sure all names are only 40 characters from a file in excel

Posted on 2012-08-11
Last Modified: 2012-08-11
I must send a data file to my bank.  They will only allow a company name of 40 characters.  QuickBooks allows me to create a excel table with all company names and other information.  But the bank will not accept anything if even one company name is longer than 40 characters.  How can I pick up only the first 40 characters of the company name?
Question by:SharonStys
    LVL 25

    Assisted Solution

    are the formulas that can sub-string out the first 40 chars from a field

    then you could do a copy and 'paste as values'
    LVL 50

    Assisted Solution


    With the company name in cell A1 use this formula in a new column


    Copy down. Then copy the results and use paste special - values to paste over the original names. After that you can delete the column with the formula.

    Cheers, teylyn
    LVL 18

    Accepted Solution

    In EXCEL 2007, 2010:

    Select the column -> Data -> Text to Columns-> Click on Fixed Width -> Click after the number of characters in the preview area ( say 40 ) which will show as a vertical line -> Finish

    This will truncate the column to 40 characters in place and you dont need to create a new column and write the formula then paste the values and then delete the old column

    Hope this helps.
    LVL 50

    Assisted Solution

    With marahman3001's approach you must insert an empty column to the right of the company name first, otherwise the existing data in the next column will be overwritten by the characters that remain after stripping the first 40.

    cheers, teylyn
    LVL 1

    Author Closing Comment

    These were all helpful and worked great.  Quick and easy to use answers.  Thanks very much.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now