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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
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!
 
Upvote 0
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 />
 
Upvote 0
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?
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,205
Members
452,618
Latest member
Tam84

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