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

or
0
0
0
s2sdefault

How to extract part of text string from cell in Excel?

Have ever tried to extract part of text string from cell? For instance, here you have a list of cells which contains 8 characters, and you want to extract first three characters from each text string as below screenshot shown, how can you quickly handle it in Excel?
doc extract part of string 1

Extract part of text string with formulas

Extract part of text string with Kutools for Excel good idea3    play

quickly extract all email addresses from text strings to another location in Excel

Sometimes, you may have a list of text strings including the email addresses in Excel, and you want to extract the email addresses to another column for convenient use. With this case, how can you solve this mission? Kutools for Excel’s Extract Mail Address utility can quickly extract the email addresses from text string in Excel. Click for 60 days free trial!
doc extract email address
 
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days.

arrow blue right bubble Extract part of text string with formulas


Here are some formulas to extract characters from string by starting from left, middle and right.

Extract characters from left

Select a blank cell next to the text string, and enter this formula =LEFT(A1,3), and press Enter key, then drag autofill handle down to apply this formula to cells. See screenshot:
doc extract part of string 2

In above formula, A1 is the text string you want to extract from, 3 is the number of characters you want to extract from left of specified text string.

Extract characters from middle

Select a cell beside the text string you want to extract from, enter this formula =MID(A1,5,2), press Enter key, then drag auto fill handle over the cells with this formula.
doc extract part of string 3

In above formula, A1 is the cell you want to extract part of string from, 5 is the position in the string you want to extract from, 2 is the number of characters you want to extract.

Extract characters from right

Select a cell next to the text string, enter this formula =RIGHT(A1,4), drag the autofill handle over cells which need to extract from right.
doc extract part of string 4

In above formula, A1 is the text string you want to extract from, 4 is the number of characters you want to extract from right.


arrow blue right bubble Extract part of text string with Kutools for Excel

In Kutools for Excel’s more than 120 powerful utilities, there are some utilities can help you extract part of text string for different purposes.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.

After free installing Kutools for Excel, please do as below:

Extract text string rounded by specific characters

For instance, you have a list of text strings, each string has some characters around with brackets, and how to extract strings within the brackets from these strings as below screenshot shown?

1. Select the cells you want to extract from, click Kutools > Text > Extract Text. See screenshot:
doc extract part of string 6

2. In the Extract Text dialog, enter the extracting criteria into the Text box, and click Add to add it into Extract list. See screenshot:

Tip: (*) indicates to extract all characters within brackets.
doc extract part of string 7

3. Click Ok, and select a position to place the extracted text string, click OK.
doc extract part of string 8

And all characters within brackets are extracted.

Note: If you want to extract only two characters after a specific character, you also can apply Extract Text.
doc extract part of string 9

Extract email address from a list

Kutools for Excel’s Extract Email Address utility can quickly extract email address from each cell.

1. Select the cells containing email addresses, click Kutools > Text > Extract Email Address. See screenshot:
doc extract part of string 10

2. In the popping dialog, select a cell to place the extracted email addresses. See screenshot:
doc extract part of string 11

3. Click OK, and all email addresses have been extracted from each cell.
doc extract part of string 12

Extract numeric or alpha characters

If you want to extract only numeric numbers, which means that you need to remove all non-numeric characters from the string, Kutools for Excel’s Remove Characters utility can do you a good favor.

1. Select the cells you want to extract numeric characters, and click Kutools > Text > Remove Characters. See screenshot:
doc extract part of string 13

2. In the Remove Characters dialog, check Non-numeric option. See screenshot:
doc extract part of string 14

3. Click Ok or Apply. And only numeric characters left.
doc extract part of string 15

Tip: For keeping original data, you had better save a copy of original data before applying this utility.


arrow blue right bubble Extract part of text string from cell


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
People in conversation:
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Sanjeeb · 5 months ago
    For an example in a cell (G2) > ABC1234568..... > is there.
    You just want to have whatever to the right after leaving the left ABC (Fixed). Then Use the formula as =MID(G2,3,200)
    Here 3 stands for after 3 character from the left and 200 is an approximate (maximum text number) number that mightbe in the right side . So your job is simple. Thank you