Separating one or multiple 5 digit numbers in a text string.

[GuyP16]

Hi All,

New here, first posting. Kinda fun.

I am working with some free text data that includes one of multiple five digit numbers I need to separate out. I have found an equation from here that pulled one code, but not all. Could be up to 8 in one free text set. I am not good with VBA, so would request help running a macro to please.

Thanks in advance,

Guy P

 

[DanteAmor]

It can be with formula or macro, but you need to give some examples of what you have and what you expect as a result. It would be better if you also say in which cell your texts start.

 

[GuyP16]

It can be with formula or macro, but you need to give some examples of what you have and what you expect as a result. It would be better if you also say in which cell your texts start.

Hey Dante,
Thank for the reply. Here is an example wit one code:

AR 8/8/16-Called ABCD of CT 800-555-0000 and spoke to Abby Y. Auth is not required for CPT code 43239 for DX code K20.0. Call ref is 12356789. I am looking to pull 43239.

Here is one with multiple 5 digit codes:

IO 3/2/17 per Elli S of ABCD 800-555-1234, call rf# 12356891, auth rq'd and no exclusions for CPT 43239, 45380 and DX K52.9, R10.33, Z83. 79, OP, covg based on med nec, codes valid and billable. Looking to pull 43239 and 45380.

Thanks!

 

[DanteAmor]

Try this

<b>Sheet</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:554px;" /><col style="width:214px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">TEXT</td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">CODES</td></tr><tr style="height:91px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >AR 8/8/16-Called ABCD of CT 800-555-0000 and spoke to Abby Y. Auth is not required for CPT code 43239 for DX code K20.0. Call ref is 12356789.</td><td style="text-align:right; ">43239</td></tr><tr style="height:55px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >IO 3/2/17 per Elli S of ABCD 800-555-1234, call rf# 12356891, auth rq'd and no exclusions for CPT 43239, 45380 and DX K52.9, R10.33, Z83. 79, OP, covg based on med nec, codes valid and billable.</td><td style="text-align:right; ">43239,45380</td></tr><tr style="height:55px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >IO 3/2/17 per Elli S of ABCD 800-555-1234, call rf# 12356891, auth rq'd and no exclusions for CPT 43239, 45380, 12345 and DX K52.9, R10.33, Z83. 79, OP, covg based on med nec, codes valid and billable.</td><td style="text-align:right; ">43239,45380,12345</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formula</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B2</td><td >=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"CPT", REPT(" ", 200)),"code",""),", ",","),200,LEN(A2)))," ",REPT(" ",99)),99))</td></tr></table></td></tr></table> <br /><br />

 

[GuyP16]

Hi Dante,

Thank you for the equation. I must be doing something wrong. I pasted the equation into the adjacent cells, some worked others returned a single lower case s.

AR 8/17/16-Called ABCD of CT 800-555-0000 and spoke to Ally D. Auth is not required for CPT codes 10061, 20680, and 20694 for DX codes L03.032, L03.031. Call ref is 25983147.

Cell jus shows an "s".
Any further thoughts?

 

[DanteAmor]

I hope the formula covers several cases.
- CPT
- CPT code
- CPT codes
- num, num
- num, num and num


=TRIM(LEFT(SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"CPT", REPT(" ", 200)),"codes",""),"code",""),", ",","),"and ",""),200,LEN(A2)))," ",REPT(" ",99)),99))

It is possible if the text does not contain any of those cases, you will not get the expected result.

 

[GuyP16]

Dante,
Thanks again for the help, but I am sorry to report thatthere are too many variations how the data is recorded to build an equationthat covers them all.
I am wondering if VB would be the way to go?
G

 

[DanteAmor]

Try this.
Texts should start in cell A2. The result will be in column B

Sub Separating_multiple_5_digit()
    Dim c As Range, cad As String
    Dim n As Variant, d As Variant, i As Long
    
    For Each c In Range("A2", Range("A" & Rows.Count).End(xlUp))
        n = Split(Replace(c.Value, ",", " "), " ")
        cad = ""
        For i = 0 To UBound(n)
            d = Val(WorksheetFunction.Trim(n(i)))
            If Len(d) = 5 Then cad = cad & d & ", "
        Next
        c.Offset(0, 1).Value = IIf(cad <> "", Left(cad, Len(cad) - 2), cad)
    Next
    MsgBox "End"
End Sub

 

[GuyP16]

Dante,
Thank you for this! I just tried to run this in VB in the spreadsheet, I keep getting an error "Invalid procedure, call or argument." I have tried to look this up and solve the issue, but am at a loss to. Any suggestions would be greatly appreciated.

I copied this from her and pasted it in the Developer window. I then try to run it and get the error.

Thanks!

Guy

 

[DanteAmor]

You can put exactly how your code was.


INSERT A MODULE
Press Alt-F11 to open the VBA editor. From the menu select Insert > Module. On the sheet that opens, paste the code previous.
Close the editor (press Alt-Q). From Excel, press Alt-F8 to open the macro selector, and select Separating_multiple_5_digit and press Run.