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

Posted on 2012-08-11
Medium Priority
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

lwadwell earned 320 total points
ID: 38284707
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

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1012 total points
ID: 38284709

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 19

Accepted Solution

Raheman M. Abdul earned 668 total points
ID: 38284719
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

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1012 total points
ID: 38284730
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

Author Closing Comment

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

840 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