Note: The other languages of the website are Google-translated. Back to English
Log in Register

How to extract string between two different characters in Excel?

If you have a list of string in Excel which you need to extract part of string between two characters from as below screenshot shown, how to handle it as quickly as possible? Here, I introduce some methods about solving this job.

Extract part string between two different characters with formulas

Extract part string between two same characters with formulas

Extract part string between two characters with Kutools for Excelgood idea3

Extract part string between two different characters with formulas

To extract part string between two different characters, you can do as this:

Select a cell which you will place the result, type this formula =MID(LEFT(A1,FIND(">",A1)-1),FIND("<",A1)+1,LEN(A1)), and press Enter key.
doc extract string between two characters 1

Note: A1 is the text cell, > and < are the two characters you want to extract string between.

Extract part string between two same characters with formulas

If you want to extract part string between two same characters, you can do as this:

Select a cell which you will place the result, type this formula =SUBSTITUTE(MID(SUBSTITUTE("/" & A3&REPT(" ",6),"/",REPT(",",255)),2*255,255),",",""), and press Enter key.
doc extract string between two characters 2

Note: A3 is the text cell, / is the character you want to extract between.

Extract part string between two characters with Kutools for Excel

If you have Kutools for Excel, you also can extract part string between two texts.

Kutools for Excel, with more than 300 handy functions, makes your jobs more easier. 
Free Download
free full-featured in 30-day

After installing Kutools for Excel, please do as below:（Free Download Kutools for Excel Now!)

1. Select a cell which will place the extracted string, then click Kutools > Formula > Formula Helper.
doc extract string between two characters 3

2. In the Formula Helper dialog, .check Filter checkbox, then type "ex" into the textbox, all formulas about extracting will be list in Choose a formula section, choose Extract strings between specified text, then go to right Arguments input section, select the cell which you want to extract substring from into Cell, then type the two texts you want to extract between.
doc kutools extract string between two texts 2

3. Click Ok, then the substring between two texts you specified has been extracted, drag fill handle down to extract subtring from each cells below.
doc kutools extract string between two texts 3

doc kutools extract string between two texts 4

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (31)
No ratings yet. Be the first to rate!
Saravana Kumar
Saravana Kumar
This comment was minimized by the moderator on the site
Hi,

U21-CMI0001-1A


How to get this between this character "CMI0001"?
Reply
Sunny
Sunny     Saravana Kumar
This comment was minimized by the moderator on the site
Hi, I do not understand your question. Do you want to extract CMI0001 from U21-CMI0001-1A or want to convert CMI0001 to U21-CMI0001-1A?

Extract: =MID("U21-CMI0001-1A",5,7)
if
Convert: ="U21"&"-"&"CMI0001"&"-"&"1A"
Reply
Report
jal
jal
This comment was minimized by the moderator on the site
how to get this between this character and refer to?
Example: 12345D76765
I want to get that "D" and this D is refer to "Daily"
Reply
Luke
Luke
This comment was minimized by the moderator on the site
In the section "Extract Part String Between Two Same Characters With Formulas";
I am trying to make a formula that extracts text after two of the same characters, i.e. extract text after / and / (which would be Judy in this case).
This is what I've got so far;
=TRIM(MID(Actions!C2,FIND(";",SUBSTITUTE(Actions!C2,";";",3))+1,255))

Any Tips, would be appreciated?
Reply
Bret
Bret
This comment was minimized by the moderator on the site
How would I extract something between two symbols, but with multiple identical symbols.Example: say A2 is the following: David~Spanish~Texas~0534~Eighth~3421. Say I wanted to extract "Texas" or the third entry in the string. Is that even possible?
Reply
Sunny
Sunny     Bret
This comment was minimized by the moderator on the site
Hi, Bret, you can try the formulas to extract substring.
FIND("~",O1,FIND("~",O1,FIND("~",O1)+1)+1) to find the position of the third ~ in the string
FIND("~",O1,FIND("~",O1,FIND("~",O1,FIND("~",O1)+1)+1)+1) to find the psition of the forth ~ in the string
MID(O1,P1+1,Q1-P1-1) extracts substring between third ~ and forth ~.
View Attachments (1 / 5)
Reply
Report
mohamed abdelraouf
mohamed abdelraouf
This comment was minimized by the moderator on the site
i had cell had numbers & characters ( 2 doll ; 5 boy toy ; 4 lines ) in need to extract 5 boy toy from this cell
Reply
Matt
Matt
This comment was minimized by the moderator on the site
Hello, unfortunately the formula is not working for me. I have my base text as: {"results":[{"id":0,"value":"TEXTA"},{"id":1,"value":"TEXTB​​​​​"}]}. I want a single formula that will extract TEXTA, with TEXTA as an unknown length. How can I do this? Thanks

Reply
Sunny
Sunny     Matt
This comment was minimized by the moderator on the site
Hello, Matt, you mean you need to extract the string between the forth "" characters? If so, sorry that is no formula I can find to solve.
Reply
Report
Terry
Terry
This comment was minimized by the moderator on the site
Hello! That magical formula worked for me! Thanks a lot :)
Reply
Oleg
Oleg
This comment was minimized by the moderator on the site
Let say I have a formulate like the one below in a cell. And I wish to do a search function with Formulatext to find "oper" and then extract the number before and after the "*". Is it possible to do that in excel?
=(1*LABFSW*4+4*LABSW*8+1*OPER14SW*8+1*TEAMSW*8)/H35


Reply
Sunny
Sunny     Oleg
This comment was minimized by the moderator on the site
Hi, Oleg, do you mean to extract all numbers before and after the text "oper"? If so, sorry I have no idea.
Reply
Report
graytech
graytech
This comment was minimized by the moderator on the site
So first of all, thanks for the formula. It helped. How, I am wanting to format the output a bit. I have a cell in Excel that has this link in it: --> https://www.google.com/search?q=how+to+create+hyperlink+in+excel+cell&oq=how+to+create+hyperlink+in+excel+cell&aqs=chrome..69i57.7896j0j15&sourceid=chrome&ie=UTF-8 <--
I need to format the output to remove and replace the "+" with a space. How can I modify the above formula to add formatting as well?
Reply
Report
Sunny
Sunny     graytech
This comment was minimized by the moderator on the site
Hi, graytech, you can use the Find and Replace function. Select the cell you use, press Ctrl + H keys to enable the Find and Replace dialog, then type + into the textbox of Find_what, type a space into the textbox of Replace_with, click Replace All button.
View Attachments (1 / 5)
Reply
Report
britech
britech
This comment was minimized by the moderator on the site
[A:174, Theater Street, Harbin, 78485][N:A H Hoteles][I:HID-535342][N:45.00€][C:32156][C:Category_4]

How I extract 45.00e or 32156? Thanks so much
Reply
Sunny
Sunny     britech
This comment was minimized by the moderator on the site
Hi, britech, I do not understand your question. Do you mean to extract 45.00e and 32156 seperately from this long string:[A:174, Theater Street, Harbin, 78485][N:A H Hoteles][I:HID-535342][N:45.00€][C:32156][C:Category_4]? Is all your data in the same length and format? Please give me more details, thank you.
Reply
Report
Jack
Jack
This comment was minimized by the moderator on the site
Hey!  Thanks for this super helpful post.

Is there a way to modify this formula that will help me target "202.020" in this string?  When i use your formula [ changing "<" to "(" ] it gives me the results from between the first pair of brackets,  not the second pair.
"C-CLASS (W202) C 180 (202.018) More information"

Result: "W202"
Desired result: "202.020"

Thanks!
Reply
Sunny
Sunny     Jack
This comment was minimized by the moderator on the site
Hi, Jack, sorry that I have no idea on your problem, either. You can post this problem to our forum https://www.extendoffice.com/forum.html, perhaps, someone can help you.
Reply
Report
joserca
joserca
This comment was minimized by the moderator on the site
Hi! Sorry if I reposted this and thank you so much for the formula.
But I'm not sure how to do this, I have this in a cell:

random comment;24/nov/21 2:34 PM;random ID;3240

and I need to get in separate cells the date without the hour, like 24/nov/21 . And, in another cell, the last numer; in this case 3240 (The format is alway the same, and the date is always 9 characters long, the only thing that might change is the last number, as is the number of seconds it took to do something)

How should I do it?
Thank you!! 
Reply
Report
Sunny
Sunny     joserca
This comment was minimized by the moderator on the site
Hi, to extract date, please use formula =MID(A1,16,9), A1 is the cell that the original data places, to extract ID number, please use formula =MID(A1,44,10),, for more details, you can refer to this articel https://www.extendoffice.com/documents/excel/3639-excel-extract-part-of-string.html
Reply
Report
Doug
Doug
This comment was minimized by the moderator on the site
HelloI have an issue where I am trying to extract a number from a set of text and numbers in excel. This is an excerpt from the data I have:AUS23-7W
GER490-8W
MEX114-2M
MEX444-5M
MEX331-3W
US118-15W
ARG572-16W
Japan122-6W
Japan526-13W
ARG585-7W
Japan398-16W
Japan320-15W
ARG141-3Mo
Canada329-5W
US421-17Mo
Canada427-14W
I need to be able to extract just the number to the right of the dash. For example, I need Japan320-15W to return just the 15 in its own column. Is there a formula you could provide me with that can accomplish this? Many thanks!

Reply
Report
Sunny
Sunny     Doug
This comment was minimized by the moderator on the site
Hello, Duggled, to solve your job, just need two steps. Firstly, select the text strings, and apply Text to Columns under Data tab, choose Delimited > Other, and type - into the textbox beside Other option, click Finish. In this step, your data has been split into two columns by deliliter -. Secondly, use the formula  =SUMPRODUCT(MID(0&B1, LARGE(INDEX(ISNUMBER(--MID(B1, ROW(INDIRECT("1:"&LEN(B1))), 1)) * ROW(INDIRECT("1:"&LEN(B1))), 0), ROW(INDIRECT("1:"&LEN(B1))))+1, 1) * 10^ROW(INDIRECT("1:"&LEN(B1)))/10)（A1 is the cell that you want to extract the number from, here you change it to your second column cell）then the numbers after - have been extracted. Or in the second step, you can apply VBA, or our handy tool, for more details, please visit:https://www.extendoffice.com/documents/excel/1622-excel-extract-number-from-string.html
View Attachments (2 / 5)
Reply
Report
Doug
Doug     Sunny
This comment was minimized by the moderator on the site
This formula works well for pretty much everything except these data :MEX114-2M
MEX444-5M
MEX373-15M
MEX540-8M
MEX396-11M
MEX386-10M
MEX369-14M
MEX446-3M
MEX75-12M
MEX372-3M
MEX178-6M
MEX510-7M
MEX361-17M
MEX501-13M
MEX221-17M
Is there any way you could revise that formula to include these? Thank you so much!
Reply
Report
Gast
Gast
This comment was minimized by the moderator on the site
Hallo,

Ik moet een stuk tekst extraheren uit een cel, maar kom er niet aan uit.

51420647 Ondersteuner Kringdirectie (Unfilled)
P_00040501 Productmanager Wendbare Pool Bedrijven (Unfilled)

De tekst die ik nodig heb ik is het middelste deel tussen de cijfers en (unfilled).
Welke formuler kan ik hiervoor gebruiken?
Reply
Anderson
Anderson
This comment was minimized by the moderator on the site
Hola,

Me encantan tus foros.

Me podrias ayudar con esto?

Tengo una serie de columnas asi:

U CALIPER R/H/R 1J0615424H Es
N DOOR WINDOW SWITCH R/H/F 8E0959851D5PR Ebbett Audi
N TAILIGHT L/H - LENS & BODY - ON QTR - W/XENON H/LAMP 63217217311 Coombes Johnson European Ltd
U*GUARD R/H/F - REPLACE RET NUT ALSO 5G0821106A (V) Private Purchase

Lo que necesito extraer es el numero de parte de cada celda pero siempre esta en una posicion diferente, por ejemplo de la primer celda necesito solo 1J0615424H, de la segunda necesito extraer 8E0959851D5PR, de la tercera 63217217311 y de la ultima 5G0821106A

Muchas gracias

Anderson
Reply
Report
Sandhya
Sandhya
This comment was minimized by the moderator on the site
Hi How to get specific value from description like
Desc: Date: 2022-07-22 23:59:51 Node: VA10TWPSQL026 Type: TSM SM_SERVER_EVENT 2579 Resource: 1700 Message Key: TSM_VA10TWPSQL026

I want to get node value as VA10TWPSQL026
Reply
Sunny
Sunny     Sandhya
This comment was minimized by the moderator on the site
Hi, Sandhya, if the node value always has a fixed length (14-chars), you can try this formula: =MID(A1,SEARCH("Node",A1)+5,14) hope it do a favor for you.
Reply
Report
GG
GG
This comment was minimized by the moderator on the site
Bonjour,

J'ai un texte AAMMJJ que je souhaite transformer en JJMMAAAA
Reply
Sunny
Sunny     GG
This comment was minimized by the moderator on the site
Hi, GG, try this formula =TEXTE(date; "jj/mm/aaaa")
Reply
Report
Mohammed Faisal
Mohammed Faisal
This comment was minimized by the moderator on the site
Hi, I have an issue which i am struggling to solve. So I have a number of answers and i want to separate into separate cells.

i.e. A: XYZ|B*: SDR|C: AQS|

I can separate but my issue is there loads of line with the asterisk moving to the correct answer for each question. How do i get my head around this?

I am currently using the below formula

=MID(A5,SEARCH("A:",cell ref)+2,SEARCH("|",A5)-SEARCH(":",cell ref)-1)
Reply
Sunny
Sunny     Mohammed Faisal
This comment was minimized by the moderator on the site
Hi, Mohannmed Faisal, from my understanding, your question is to extract XYZ, SDR and AQS separately into three cells. If so, please use formulas below separately:
=MID(A7,SEARCH("A:",A7)+3,3)
=MID(A7,SEARCH("B*:",A7)+4,3)
=MID(A7,SEARCH("|C:",A7)+4,3)
A7 is the cell that you use to extract.
Hope it help you.
Reply
Report
There are no comments posted here yet
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations