Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to extract all but first/last character from string in Excel?

In Excel, extracting the first n or last n characters may be easy for most Excel users, but if you want to extract all characters but the first or last character, how could you solve it? Here I tell some ways for quickly extracting all characters but firs/last character in Excel.

Extract all but first/last n characters from string with formula

Extract all but first/last n characters from string with Kutools for Excel

Excel Productivity Tools

Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial

Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately. 60-day Unlimited Free Trial


arrow blue right bubble Extract all but first/last n characters from string with formula


Extract all but first one characters from string

Select a blank cell and type this formula =MID(A1, 2, LEN(A1)) (A1 is the cell you want to extract all but the first one character from, the number 2 is the character's order you want to begin extracting at, in this case, I extract all but first character so I extract character from the second character), press Enter button and fill the range you want. See the result screenshot:

Extract all but last one characters from string

Select a blank cell and type this formula =LEFT(A1,LEN(A1)-1) (A1 is the cell you want to extract all but the last one character from, the number 1 is the number you do not want to extract from right of the string), press Enter button and fill the range you want. See the result screenshot:


arrow blue right bubble Extract all but first/last n characters from string with Kutools for Excel

If you have Kutools for Excel installed in your computer, you can use the Remove by Position feature to extract all characters but first or last n characters in Microsoft Excel.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now

1. Select the range cells you want to extract, and click Kutools > Text Tools > Remove by Position. See screenshot:

2. In the Remove by Position dialog, specify the number you do not want to extract from the string in the Numbers text box, and check From left. See screenshot:

3. Click Ok or Apply, the first n characters are remove from each string.

If you want to extract all characters but last n characters, check From right check box in step 2.

Tip: Applying this feature will change your original data, you’d better copy them firstly.

Click here for more on Remove by Position.


Relative Articles:


Excel Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 70%, and Help You To Stand Out From Crowd

Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for you.

  • Designed for 1500+ work scenarios, helps you solve 80% Excel problems.
  • Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  • Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
  • Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
  • 60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  • Being used by 110,000 elites and 300+ well-known companies.

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    gatanata · 2 years ago
    Very thankful for the post. I was trying few hours with different kind of formulas when finally I found this post and resolved in seconds.
    And this Kutools is great tool but still no free version unfortunately.
  • To post as a guest, your comment is unpublished.
    Rick Nickerson · 3 years ago
    I want to extract all but the first word from a cell. I can extract the first word but I want the First word in one column and the rest of the string to appear in another column without the extracted word in it.

    Any help would be great!

    Thanks,
    Rick