View unanswered posts | View active topics It is currently Wed Oct 16, 2019 4:31 am

Reply to topic  [ 2 posts ] 

How to truncate an excel string



How to truncate an excel string 
Author Message
User avatar

Joined: Fri Feb 09, 2018 2:06 pm
Posts: 14
Post How to truncate an excel string
Hi all, I thought I would share this handy little how to:

Recently I needed to edit approximately 2,000 lines in a CSV file in order to allow it to be imported into an ecommerce site. The situation was that a number of strings in the file were greater than the maximum 100 allowed by the ecommerce site.. obviously not practical to do them all individually.. so here's a nice little formula you can use:

- Select a blank cell immediately to the right of the string you want to truncate (insert a column if you need to or select a cell to the left and change 'LEFT' to 'RIGHT' in the formula below

- Enter this formula =LEFT(A1,6) (A1 is the cell of the string you wish to truncate, and 6 indicates truncate the string to six characters. Of course you can choose what number to truncate to as per your requirements)

- Copy and paste the formula into the spread-sheet as required

- If like me you need to then remove the additional column you added to carry out the task then you now need to simply copy all the truncated cells and 'Paste Special' 'Values' back to your original column to overwrite the strings that were too long

- Delete the temporary column and voila! Save your spread-sheet.

Fri Feb 09, 2018 2:23 pm
Profile WWW
User avatar

Joined: Wed Feb 03, 2010 1:45 pm
Posts: 283
Location: Stoke-On-Trent
Post Re: How to truncate an excel string
nice guide, thanks for sharing!

** I don't need to suck up, I pay the internet bill, my GF is addicted to facebook :)

Fri Feb 09, 2018 3:32 pm
Display posts from previous:  Sort by  
Reply to topic   [ 2 posts ] 

Who is online

Users browsing this forum: No registered users and 1 guest

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: