Excel VBA function to extract substring from longer string

Thank you for looking at my question,

I have a poorly formatted .csv file that I am need to distill down so that I can import it into MS Access.

The process I am following is to read lines from the csv file, carry out some string manipulation and write to a .xlsx worksheet.

Part of the process Is that I need to extract a sub-string of varying length from a specific line within the file.

The text in question is:

<BOM Line (Parent Node)>          1912009989                            Opt 1000 25S wired inner assy.

and I need to be able to extract the sub-string that is, in this case, 1912009989. In subsequent files the sub-string may be longer and I cannot say whether the number of spaces between the ">" character and the first character of the sub-string will always be as in this instance.


I need the sub-string that is the first character after ">" that isn't a space to the last character before the next space


Any help you can offer will be appreciated greatly
Gary CroxfordOperations Support AnalystAsked:
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.

Ryan ChongCommented:
try

Sub Button1_Click()
    Dim test
    test = getIDNo("<BOM Line (Parent Node)>          1912009989                            Opt 1000 25S wired inner assy.")
    MsgBox test
End Sub

Function getIDNo(v As String) As String
    Dim arr() As String
    arr = Split(v, " ", , vbTextCompare)
    For i = 0 To UBound(arr)
        If IsNumeric(arr(i)) Then
            getIDNo = arr(i)
            Exit Function
        End If
    Next
    getIDNo = ""
End Function

Open in new window


assuming:
1. there are spaces before and after the ID No.
2. first numeric no is the ID No.
0
Gary CroxfordOperations Support AnalystAuthor Commented:
Ryan, Thank you for you response.

There are spaces before and after the ID No but the first character of the ID could be either a number or a letter.

How do I need to change the getIDNo function to reflect this?
0
Rgonzo1971Commented:
Hi,

pls try this function

Function getItem(s As String) As String
    Dim arr1() As String
    Dim arr() As String
    arr1 = Split(s, ">", , vbTextCompare)
    getItem = Split(Trim(arr1(1)), " ", , vbTextCompare)(0)
End Function

Open in new window

Regards
0

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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

ProfessorJimJamCommented:
Rgonzo UDF works in your case if your string always have those comparative operators , but will not work in .xlsx worksheet as you mentioned,  to work with a normal .xlsx formula you need to use built-in Functions to achieve the result.

here is my formula solution with excel built-in functions

=LOOKUP(9.99E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))

Open in new window

0
Rory ArchibaldCommented:
The question title begins with "Excel VBA function", so... ;)
0
ProfessorJimJamCommented:
Yes Rory :-)

now, i read the title.  well in that case Rgonzo1971 nailed it with UDF.
0
Gary CroxfordOperations Support AnalystAuthor Commented:
RGonzo1971, This works fine but can you explain to me what it is doing
0
Rgonzo1971Commented:
arr1 is an array of strings splitted at > form the original string

<BOM Line (Parent Node)
          1912009989                            Opt 1000 25S wired inner assy.

I then use the second element of the array (1) since it is zero based

          1912009989                            Opt 1000 25S wired inner assy.

delete the spaces before and after the text thanks to trim
1912009989                            Opt 1000 25S wired inner assy.
then creating an array of strings separated with the space

1912009989

(...)
Opt
1000
25S
wired
inner
assy.

and taking the first element of this array(0)
1912009989
1
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.