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

GuyP16

New Member
Joined
Mar 26, 2019
Messages
17
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Mar 26, 2019
Messages
17
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Mar 26, 2019
Messages
17

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.
 
Last edited:

GuyP16

New Member
Joined
Mar 26, 2019
Messages
17

ADVERTISEMENT

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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try this.
Texts should start in cell A2. The result will be in column B

Code:
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

New Member
Joined
Mar 26, 2019
Messages
17
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,631
Messages
5,523,992
Members
409,554
Latest member
denistrevisan

This Week's Hot Topics

Top