Solved

Need help changing functions to strip strings and numbers from the same column

Posted on 2014-01-31
4
287 Views
Last Modified: 2014-01-31
I'm working with a pair of scripts I found online and they almost work for my purposes but not quite.  In the content I'm using it, I have a long string comprised of numbers and letters - accounting codes and accounts to be precise.  The script that takes the letters does so admirably, but some of the account names have numbers in them so they are dropped.  The other script that takes the numbers also removes any spaces between the numbers that are needed later.

So, for instance a column that contains something like:
'10 5200 302 2077 9999 211 3   TR 401 ABC DEF GHI'
with chars getChars script, I get 'TR ABC DEF GHI' - missing the 401
and the getNums script returns 105200302207799992113 - missing the spaces.

I've modified the numeric script to include spaces but then it pulls any number from anywhere and I'm only interested in the first portion of the string. So, while I get the correct   10 5200 302 2077 9999 211 3 , I also get the 401 from the account name.

I was hoping to keep this in functions so it will be easy for our finance team to run it themselves - too complicated and it will land on my desk every time it needs to be split - which I'd like to avoid.

While the test data and the sample file have 3 spaces between the numbers and letters, there's not guarantee that will continue in the future.  The only thing I can think of for the numbers is to find the first letter in the string and somehow crawl backwards, find the last number and then get everything from the beginning to that point - but I'm sure there must be a better way.

My current iteration of the scripts are:

Function GetChars(target As Range)
    Dim MyStr As String, i As Integer
    MyStr = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If Not IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
    Next i
GoExit:
    GetChars = Trim(MyStr)
End Function

Function GetNums(target As Range)
    Dim MyStr As String, i As Integer
    MyStr = ""
    If Len(target.Value) = 0 Then GoTo GoExit
    For i = 1 To Len(target.Value)
        If IsNumeric(Mid(target, i, 1)) Or Asc(Mid(target, i, 1)) = 32 Then MyStr = MyStr & Mid(target, i, 1)
    Next i
GoExit:
    GetNums = MyStr
End Function

Open in new window


Sample data could be:

10 1098   AD AAA BBB Retreat   BP
10 1220   AD Abc Def   JAF
10 1230   AD Abc Def   SSSC
10 5200 360 1647 9999 100 3   AD 401 Xyz Abc Def   JAF
10 5200 360 1731 9999 120 3   AD 301 Ghi Jkl Mno   ETRC
10 5200 360 1732 9999 180 3   AD 302 Pqr Stu Vwxyz   CASC
10 5200 360 1732 9999 211 3   AD 302 Abc Def Hijkl   BP
10 8910 304 4002 9999 110 3   FA CP GED Dallas
10 8910 350 1499 9999 100 3   FA ABCD MBR   JAF

Open in new window


Any help would be greatly appreciated and, as always, many thanks in advance.
0
Comment
Question by:saabStory
  • 2
4 Comments
 
LVL 52

Expert Comment

by:Bill Prew
ID: 39825819
I think we could probably do something with regex, but I won't be able to poke at it until late tomorrow.  I'll keep an eye on the question.

~bp
0
 
LVL 52

Expert Comment

by:Bill Prew
ID: 39825820
What exactly is the output that you want from the sample input strings you provided?

~bp
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 500 total points
ID: 39825845
See if these work for you:
Function GetChars(target As Range)
    Dim myStr As String, myChar As String, i As Integer

    If Len(target.Value) > 0 Then
        For i = 1 To Len(target.Value)
            myChar = Mid(target, i, 1)
            If Not IsNumeric(myChar) And myChar <> " " Then Exit For
        Next i
        myStr = Mid(target, i)
    End If
    GetChars = Trim(myStr)
    
End Function

Function GetNums(target As Range)
    Dim myStr As String, myChar As String, i As Integer

    If Len(target.Value) > 0 Then
        For i = 1 To Len(target.Value)
            myChar = Mid(target, i, 1)
            If Not IsNumeric(myChar) And myChar <> " " Then Exit For
        Next i
        myStr = Mid(target, 1, i - 1)
    End If
    GetNums = Trim(myStr)
    
End Function

Open in new window

Ron
0
 

Author Closing Comment

by:saabStory
ID: 39825888
That's great - that will make our finance department very happy.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now