Truncating Text Data within Cells

Feb 27th, 2002, 05:15 PM

I have a column of 4000 cells with text data of varying size in each cell. I only need the first 8 characters in each cell and I want to truncate (trash) the rest of the data. Any ideas. I tried to set up a validation in a new column, but this did not work. Thanks in advance.

Juan Pablo González · Feb 27th, 2002, 05:19 PM

Select the 4000 cells, go to Data, Text to Columns, select "Fixed With", and drag a line that divides the first 8 characters from the rest. Click Finish.

Feb 27th, 2002, 05:26 PM

Thanks Juan, This moves the remaining charaters into the adjacent column, is there any way to have it just delete the left over characters?

Juan Pablo González · Feb 27th, 2002, 05:27 PM

Sure. Instead of clicking "Finish", click "Next", then, you have the option to "Format" each column. In there, select the second column, and choose "Skip this column". Then you can click Finish.

Feb 27th, 2002, 05:35 PM

OK, maybe I'm just stupid (as you nod your head), but does this have anything to do with the destination field? When I hit skip, it simply deletes the characters that I need and not the ones that I don't. In this final step the next button is grayed out. urrrrgh.

Juan Pablo González · Feb 27th, 2002, 05:37 PM

You're selecting the first column, try selecting the second one, and click "Skip".

Feb 27th, 2002, 05:19 PM	#2
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,757	Select the 4000 cells, go to Data, Text to Columns, select "Fixed With", and drag a line that divides the first 8 characters from the rest. Click Finish. __________________ Regards, Juan Pablo González http://www.juanpg.com

Feb 27th, 2002, 05:27 PM	#4
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,757	Sure. Instead of clicking "Finish", click "Next", then, you have the option to "Format" each column. In there, select the second column, and choose "Skip this column". Then you can click Finish. __________________ Regards, Juan Pablo González http://www.juanpg.com

Feb 27th, 2002, 05:37 PM	#6
Juan Pablo González MrExcel MVP Join Date: Feb 2002 Location: Bogota, Colombia Posts: 11,757	You're selecting the first column, try selecting the second one, and click "Skip". __________________ Regards, Juan Pablo González http://www.juanpg.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 27th, 2002, 05:15 PM	#1
Guest Posts: n/a	I have a column of 4000 cells with text data of varying size in each cell. I only need the first 8 characters in each cell and I want to truncate (trash) the rest of the data. Any ideas. I tried to set up a validation in a new column, but this did not work. Thanks in advance.

Feb 27th, 2002, 05:26 PM	#3
Guest Posts: n/a	Thanks Juan, This moves the remaining charaters into the adjacent column, is there any way to have it just delete the left over characters?

Feb 27th, 2002, 05:35 PM	#5
Guest Posts: n/a	OK, maybe I'm just stupid (as you nod your head), but does this have anything to do with the destination field? When I hit skip, it simply deletes the characters that I need and not the ones that I don't. In this final step the next button is grayed out. urrrrgh.