Extract specific value using SEARCH FORMULA

[Guest]

Hi,

Below is text and underneath is a formula to extract a number (53625) from
Text after "PMT DET". Since this number is broken down between two rows, it
is not extracting the value as desire and gives "#value! error. How can fix
the formula?

thanks in advance.

Dinesh

Text
"WIRE TYPE:WIRE OUT DATE:060231 TIME:1219 ET
TRN:3001014500160362 SERVICE REF:002067
BNFISTRIBUTED SYSTEMS SERVIC ID:ab68068pc
BNF BK:NATIONAL BENN CORP ID:031308784 PMT DET:536
25 KMC-65069 INV a000G5202 0000ab /152
"
Formula
=IF(ISERROR(SEARCH("WIRE TYPE:WIRE",D25)),"",MID(D25,SEARCH("PMT
DET:",D25)+8,5)+0)&IF(ISERROR(SEARCH("WIRE
TYPE:BOOK",D25)),"",MID(D25,SEARCH("REF:",D25)+4,5)+0)

 

[KL]

=N(CLEAN(IF(ISERROR(SEARCH("WIRE TYPE:WIRE",D25)),"",MID(D25,SEARCH("PMT DET:",D25)+8,5))&IF(ISERROR(SEARCH("WIRE
TYPE:BOOK",D25)),"",MID(D25,SEARCH("REF:",D25)+4,5))))

--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile: https://mvp.support.microsoft.com/profile=AB32F8EE-8ED3-4AB9-ADDA-9E6C73C09A36

 

[Ron Rosenfeld]

Hi,

Below is text and underneath is a formula to extract a number (53625) from
Text after "PMT DET". Since this number is broken down between two rows, it
is not extracting the value as desire and gives "#value! error. How can fix
the formula?

thanks in advance.

Dinesh

Text
"WIRE TYPE:WIRE OUT DATE:060231 TIME:1219 ET
TRN:3001014500160362 SERVICE REF:002067
BNFISTRIBUTED SYSTEMS SERVIC ID:ab68068pc
BNF BK:NATIONAL BENN CORP ID:031308784 PMT DET:536
25 KMC-65069 INV a000G5202 0000ab /152
"
Formula
=IF(ISERROR(SEARCH("WIRE TYPE:WIRE",D25)),"",MID(D25,SEARCH("PMT
DET:",D25)+8,5)+0)&IF(ISERROR(SEARCH("WIRE
TYPE:BOOK",D25)),"",MID(D25,SEARCH("REF:",D25)+4,5)+0)

If the length of the text will be less than 256 characters, you could download
and install Longre's free and easily distributable morefunc.xll add-in from
http://xcell05.free.fr

and then use this Regular Expression formula:

=IF(REGEX.COMP(A1,"WIRE TYPE:WIRE"),
REGEX.SUBSTITUTE(REGEX.MID(A1,"(?<=PMT DET[\d\n]+"),"\n"),"")


--ron

 

[Ron Rosenfeld]

Hi,

Below is text and underneath is a formula to extract a number (53625) from
Text after "PMT DET". Since this number is broken down between two rows, it
is not extracting the value as desire and gives "#value! error. How can fix
the formula?

thanks in advance.

Dinesh

Text
"WIRE TYPE:WIRE OUT DATE:060231 TIME:1219 ET
TRN:3001014500160362 SERVICE REF:002067
BNFISTRIBUTED SYSTEMS SERVIC ID:ab68068pc
BNF BK:NATIONAL BENN CORP ID:031308784 PMT DET:536
25 KMC-65069 INV a000G5202 0000ab /152
"
Formula
=IF(ISERROR(SEARCH("WIRE TYPE:WIRE",D25)),"",MID(D25,SEARCH("PMT
DET:",D25)+8,5)+0)&IF(ISERROR(SEARCH("WIRE
TYPE:BOOK",D25)),"",MID(D25,SEARCH("REF:",D25)+4,5)+0)

If the length of the text will be less than 256 characters, you could download
and install Longre's free and easily distributable morefunc.xll add-in from
http://xcell05.free.fr

and then use this Regular Expression formula:

=IF(REGEX.COMP(A1,"WIRE TYPE:WIRE"),
REGEX.SUBSTITUTE(REGEX.MID(A1,"(?<=PMT DET[\d\n]+"),"\n"),"")


--ron

If your text string might be longer than 255 characters, then you can use this
VBA UDF.

To enter the UDF, <alt-F11> opens the VB Editor.
Ensure your project is highlighted in the Project Explorer window, then
Insert/Module and paste the code below into the window that opens.

To use the UDF, in some cell enter the formula:

=PMTDET(cell_ref,"WIRE TYPE:WIRE")

The formula will return the number that exists after the string "PMT DET:" and
ignore any "new line" characters.

If your format is different, the regex might need to be changed a bit to
accomodate.

--------------------------------------------------
Option Explicit

Function PMTDET(str As String, RecType As String) As String
Dim objRegExp As Object
Set objRegExp = CreateObject("VBScript.RegExp")

Dim colMatches As Object
Const sPattern As String = "(PMT DET([\d\n]+)"

With objRegExp
.IgnoreCase = False
.Global = True
.Pattern = RecType

If .Test(str) = True Then
.Pattern = sPattern
If .Test(str) = True Then
Set colMatches = .Execute(str)
PMTDET = colMatches(0).submatches(1)
PMTDET = Replace(PMTDET, Chr(10), "")
End If
End If

End With

End Function
============================================
--ron