Extract data from a cell

Hi
I need some advice, I am having an issue extracting data, I am using the info contained in a subject header to count orders placed (there is no better way currently as this is what I inherited, this works fine when there is only one order number in the subject field, the issue I am having is when there are multiple order numbers contain in the subject field,

All order numbers are 8 digits long and start with 22, the agent will also put in there initials like so "PGW22247909cl" which is any easy fix the issue is with subject headers like this "pkc22375826223758402237588622375898JC" can you think of a way to separate out so I can count the 4 orders? I hope this makes sense, I have an example sheet if needed

Andrew
Walker85Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

tel2Commented:
Hi Walker,

"All order numbers are 8 digits long and start with 22, the agent will also put in there initials like so "PGW22247909cl" which is any easy fix..."
You haven't mentioned what the "cl" on the end is.  What is it?

"I have an example sheet if needed."
Good - please post it.

Thanks.
0
Walker85Author Commented:
Hi
 They are  the initials, the issue I have Is before I came into job the hadn't standardised what had to go into subject header so as you will see in spreadsheet there is stuff it there like PO numbers and text etc as well as order numbers

I have attached the file as an example but is very rough attempt at mo
Template-test.xlsx
0
Saurabh Singh TeotiaCommented:
Their you go..i wrote a udf for you which does what you are looking for..

Function getorder(rng As Range)

    Dim str As String, i As Long, k As Long

    For i = 1 To Len(rng.Value)

        If Mid(rng.Value, i, 1) = 2 And Mid(rng.Value, i, 2) = 22 And IsNumeric(Mid(rng.Value, i, 8)) Then

            If str = "" Then
                str = Mid(rng.Value, i, 8)
            Else
                str = str & "," & Mid(rng.Value, i, 8)
            End If
            i = i + 8

        End If


    Next i

getorder = str
End Function

Open in new window


Check Column-G highlighted in Yellow...

Saurabh...
Template-test.xlsm
0
Walker85Author Commented:
WOW how did you do this???? how would I implement it into a new sheet? you are my new hero
0
Saurabh Singh TeotiaCommented:
Walker,

Thanks for kind words..Now On your workbook..Press alt+f11 which will be open the visual basic editor..now in their goto insert-->new module--> and copy & paste the code their of this function..

Now close the visual basic editor and come to your worksheet where you want to apply this formula..and then you can get a formula with the name of getorder and apply in the same manner as i did and you will be all set..

Saurabh
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.