Help! pull specific text from cell

[Yatocm]

I'm trying to figure out a way to pull specific text from a long mesh of test in a cell (R). For example,

"SF123811#ARR676#Unbilled AR Trueup" this is an example of what could be in cell R

<tbody>
</tbody>

and this formula works when I'm trying to pull the ARR number
=LEFT(MID(R2,FIND("#",R2)+1,LEN(R2)),FIND("#",MID(R2,FIND("#",)+1,LEN(R2)))-1)

But not all the data in cell R is the same as another example in cell R123:

"Reversal Of Prior Debit Unbilled Receivable Adjustment for Revenue Arrangement for ARR4628, Element: ELEM35240" so finally to my question: Is the something that could always get me the ARR number no matter where it is and no matter how long it is? (ARR12 as well as ARR123456789).

<tbody>
</tbody>

 

[lrobbo314]

Here is a VBA solution...

Function ARR(s As String) As String
Dim RX As Object: Set RX = CreateObject("vbscript.regexp")
Dim Pattern As String: Pattern = "ARR\d+"


With RX
    .Pattern = Pattern
    .Global = True
    .MultiLine = True
    .ignorecase = True
    Set matches = RX.Execute(s)
End With


If matches.Count > 0 Then
    ARR = matches(0)
Else
    ARR = "N/A"
End If


End Function

 

[jtakw]

Hi,

Assumes the ARR number is Always in CAPS, this works for the samples provided.

If there are Other variations, I would need more samples.

Book1
	A	B
1	SF123811#ARR676#Unbilled AR Trueup	ARR676
2	Reversal Of Prior Debit Unbilled Receivable Adjustment for Revenue Arrangement for ARR4628, Element: ELEM35240	ARR4628
3	Reversal Of Prior Debit Unbilled Revenue Arrangement for ARR123456789, Receivable Adjustment for Element: ELEM35240	ARR123456789
Sheet184

Cell Formulas
Range	Formula
B1	=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("ARR",A1),255),",","#"),"#",REPT(" ",100)),100))

Formula copied down.

 

[Yatocm]

thank you, that works perfectly

 

[jtakw]

You're welcome.