[Last Call] Learn how to a build a cloud-first strategyRegister Now


Find Text in String and return the values after that text with excel

Posted on 2012-08-31
Medium Priority
Last Modified: 2012-09-07
Hello Experts,

I am trying to find a specific piece of text in excel.  The workbook has a column that has a long string of text in each row(A5:A10000). I  want to return the text that is located after the first search. e.g( this is the long string ID=135 test test tdasl;kj )  I would like to find the text" ID=" and return the number 135 after it.  

Is there a function that could help me do this?

Hope this makes sense.
Question by:skyzipper
LVL 50

Accepted Solution

barry houdini earned 1500 total points
ID: 38355633
Is it always 3 digits? If so try this formula in B5 copied down


or for a variable number of digits (1 to 5) try


If you double-click on the fill-handle of B5 (bottom right of the cell) then that will fill the formula down automatically as far as you have continuous data in column A

regards, barry
LVL 19

Expert Comment

by:Ken Butters
ID: 38355640
This solution assumes that you have a space after the ID=XXXX

=MID(A1,FIND("ID=",A1)+3,FIND(" ",A1,FIND("ID=",A1))-3)
LVL 24

Expert Comment

ID: 38355734
You could use the Data > Text to columns > and use 'space' and '=' as delimited.
This will split the text into nice convenient columns.
LVL 46

Expert Comment

ID: 38356260
If you wrap the VB Val() function in a public user defined function (like the StringVal function below, you can simplify your parsing and value extraction.
Option Explicit

Public Function StringVal(parmString As String) As String
    StringVal = Val(parmString)
End Function

Open in new window

In a formula, it would look something like this:
LVL 18

Expert Comment

ID: 38356594
To know possible "gotchas" that you have to watch out for while using Val function, see



Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question