Home |
Search |
Today's Posts |
#1
|
|||
|
|||
fixed column width with text string
I am importing a file into Excel. This Excel file will be read by an
automated system and needs to be formatted in a very precise way. Each field from the data source (name, for example) is automatically inputed into a column in Excel. In order for the automated system to read this file, each column must be a specific number of characters long, with blanks or zeroes making up the difference. For example, if the name was "smith" and the "name" column is supposed to be 40 characters long, the remaining 35 characters would have to be accounted for somehow. I want the final product in Excel to be a text string (no spaces, commas, columns, etc between each). I will be running this report regularly, so the simpler the solution, the better. Thanks! |
#2
|
|||
|
|||
Hi, Sarah-
Here's one way: using your "Smith" example in a field that must be 40 characters long, enter the name Smith into cell A1. In cell B1 type this formula: =A1&REPT(" ",40-LEN(A1)) This formula determines the length of the name in A1, subtracts that number from 40, then adds that number of spaces to the name in A1. You can copy that formula and Paste As values to convert the results into text. Dave O |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export to fixed width text file | Excel Discussion (Misc queries) | |||
Column Width Behavior | Excel Discussion (Misc queries) | |||
Putting text in a column based on variable text from another colum | Excel Discussion (Misc queries) | |||
Counting every unique text string in a column | Excel Worksheet Functions | |||
Change the width of a single column in a column chart | Charts and Charting in Excel |