Format number of characters in cell

[capitalmediagroup]

Hi there,

I've got a column within Excel which contains account references in the format of straight letters. For example: WOODYSBARN

Basically, we need to truncate all of those account references to be 8 characters rather than 10, shaving two letters off the end. Trouble is, there's 3,900 records - and we don't want to do this manually.

Is there any formatting rule we can use to remove a couple of letters off the end of the words in each cell we require?

I'd be grateful if you would save my sanity....

Kind regards
Stuart

 

[pagrender]

If you want WOODYSBARN to become WOODYSBA, you can use this formula:

=LEFT(A1,8)

 

[buxtongt]

Try =left(a1,8) assuming your full field is in a1. You can insert a column and do this for all of your records. Hope this helps.

 

[jasonb75]

Welcome to the board!

If you change the format of the cells the extra characters will still be there, just hidden, although I'm not entirely sure you can truncate text with fomatting in the same way you can numbers.

Try using text to columns, fixed width, set to 8 characters wide and choose "do not import column (skip)" for the second column.

 

[capitalmediagroup]

Hi Jason

Thanks for this. How specifically do I do what you've suggested?

The column is already there, do I select the whole column and use an option from the toolbar?

Thanks
Stuart

 

[Rick Rothstein]

The column is already there, do I select the whole column and use an option from the toolbar?

What version of Excel are you using?

 

[capitalmediagroup]

I'm using Excel 2007

Thanks

 

[Rick Rothstein]

I'm using Excel 2007

Select the whole column, then select the Data tab and click the "Text to Columns" button on the "Data Tools" panel. When the dialog box appears, select "Fixed width" and click the Next button. In the "Data preview" table, click at position 8 on the "ruler" (if you miss, you can slide the line that appears back and forth as needed) and then click the Next button. Make sure the Destination field is showing the address for the first cell in the selected column (it should do this by default), then click the anywhere in the second column showing in the table and put a mark in the "Do not import column (skip)" option button, then click the Finish button.

 

[capitalmediagroup]

Thank you so much, I'll give this a go tomorrow!

Best
Stuart