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

x
Solved

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

Posted on 2012-08-31
Medium Priority
532 Views
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.
0
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

=MID(A5,FIND("ID=",A5)+3,3)+0

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

=LOOKUP(10^5,MID(A5,FIND("ID=",A5)+3,{1,2,3,4,5})+0)

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
0

LVL 19

Expert Comment

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)
0

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.
0

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
``````

In a formula, it would look something like this:
=StringVal(MID(D3,FIND("ID=",D3)+3,100))
0

LVL 18

Expert Comment

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

http://www.excelfox.com/forum/f23/val-function-436/

Kris
0

## Featured Post

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.
###### Suggested Courses
Course of the Month18 days, 2 hours left to enroll