Solved

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

Posted on 2012-08-31
526 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

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

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

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 44

Expert Comment

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

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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.