Shortening pre-existing data in cells in a particular column

[Guest]

Lets say I have a column of data (of text in each cell) that already exists.
I want to automatically cut off any characters past the 16th character if
they exist (just like if I had run the substr command in SQL). Is there a
way to do that column for column on data that already exists?

Data validation only prevents new data from exceeding that length, but these
files are flat files generated by the database on startup. So any help would
be appreciated on this. Thanks,

 

[Guest]

witrh you column of data in column a put this in b1 and drag down or double
click the fill handle:-

=LEFT(A1,16)

Mike

 

[Pete_UK]

Let's say your data is in column A, and covers up to column L, and
that you have a header row. You can enter this formula in M2:

=IF(LEN(A1)>16,LEFT(A1,16),A1)

If you now copy this down column M it will truncate all data to 16
characters and leave shorter data alone. You can copy the formula
across row 2 to get it applied to other columns, although you might
not want to apply it to numeric data.

You can then highlight all the data in column M (and other columns if
required) and click <copy>, then Edit | Paste Special | Values (check)
| OK then <Esc> - you have now fixed those values, so they are not
dependent on the original data, and if you wish you can copy column M
to overwrite the data in column A, and so on.

Hope this helps.

Pete

 

[Guest]

Select the column and use Data->Text to columns->Fixed width->click on the
header to create a break after the 16th character (be sure to eliminate any
other breaks). In the next step tell Excel to skip the 2 column

 

[Guest]

Use the LEFT function to get the 16 characters that you need.

Make a back up of your file and then insert a column to the right of the
data you want to truncate. Assuming that the data is in column A row 1,
place the following formula in B1:

=LEFT(A1,16)

Copy the formula down column B to the last cell.

Then copy all the formula cells in column B and move to A1. Click EDIT on
the menu, select PASTE SPECIAL and select VALUES and click OK. Delete
column B from the worksheet and you've replaced all the original value with
their 16 character counterpart.