Truncating Text Data within Cells

G

Guest

Guest
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0
Thanks Juan, This moves the remaining charaters into the adjacent column, is there any way to have it just delete the left over characters?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You could cleanly have this in a bordering column by entering below equation. I will assume you want to truncate text in A1:

=left(a1,8)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top