Help! pull specific text from cell

Yatocm

New Member
Joined
Feb 27, 2018
Messages
12
I'm trying to figure out a way to pull specific text from a long mesh of test in a cell (R). For example,


[TABLE="width: 546"]
<tbody>[TR]
[TD="class: xl125, width: 546"]"SF123811#ARR676#Unbilled AR Trueup"

this is an example of what could be in cell R[/TD]
[/TR]
</tbody>[/TABLE]

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:

[TABLE="width: 546"]
<tbody>[TR]
[TD="class: xl126, width: 546"]"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).[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Here is a VBA solution...

Code:
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
 
Upvote 0
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
AB
1SF123811#ARR676#Unbilled AR TrueupARR676
2Reversal Of Prior Debit Unbilled Receivable Adjustment for Revenue Arrangement for ARR4628, Element: ELEM35240ARR4628
3Reversal Of Prior Debit Unbilled Revenue Arrangement for ARR123456789, Receivable Adjustment for Element: ELEM35240ARR123456789
Sheet184
Cell Formulas
RangeFormula
B1=TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(MID(A1,FIND("ARR",A1),255),",","#"),"#",REPT(" ",100)),100))


Formula copied down.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top