Skip to main content

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

Best Office Productivity Tools

Supercharge Your Spreadsheets： Experience Efficiency Like Never Before with Kutools for Excel

Popular Features: Find/Highlight/Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns   |   Move Columns   |   Unhide Columns   |   Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolset:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   Many More...

Kutools for Excel boasts over 300 features, ensuring that what you need is just a click away...

Supports Office/Excel 2007-2021 & newer, including 365   |   Available in 44 languages   |   Enjoy a full-featured 30-day free trial.

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!
Comments (35)
No ratings yet. Be the first to rate!
cc
cc
This comment was minimized by the moderator on the site
Bonjour,

je souhaite extraire de ce texte (USD) 934.915765 (30-11-2022) le numero 934.915765, ce que je souhaite c'est faire un extrait de tout ce qui est entre (USD) et (30-11-2022)
Reply
0
0
Sunny
Sunny     cc
This comment was minimized by the moderator on the site
Hi, please try this formula:
=LEFT((MID(I1,FIND(" ",I1)+1,256)),FIND(" ",(MID(I1,FIND(" ",I1)+1,256)),1)-1)
I1 is the cell that you want to extract number.
Reply
Report
0
0
Sunny
Sunny
This comment was minimized by the moderator on the site
Hi, try this formula please:
=IFERROR(MID(A1,FIND("CG",A1),11),IFERROR(MID(A1,FIND("CP",A1),11),IFERROR(MID(A1,FIND("CV",A1),11),IFERROR(MID(A1,FIND("CD",A1),11),IFERROR(MID(A1,FIND("CC",A1),11),MID(A1,FIND("CO",A1),11))))))

If there is no match string, it returns #VALUE! error.
Reply
Report
0
0
Ciprian
Ciprian
This comment was minimized by the moderator on the site
Hello, can you please help me with extracting a code from a cell? The code is always 11 characters long and it can start with either CC, CP, CV, CO, CG or CD. If it helps, it also ends with a number always. The code does not have a fixed position, it can be in the beginning, middle or end.
I tried this formula =MID(A2,FIND("CP",A2),11) but, as you see, it only finds the ones starting with CP.

An example of what the cell might contain is:
Text text CG1234dh201 text 123 text

Thank you!
Reply
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
anderz777
anderz777
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
0
0
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
0
0
Duggled
Duggled
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
0
0
Sunny
Sunny     Duggled
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
0
0
Duggled
Duggled     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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
Terry
Terry
This comment was minimized by the moderator on the site
Hello! That magical formula worked for me! Thanks a lot :)
Reply
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
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
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations