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 iGoExit: GetChars = Trim(MyStr)End FunctionFunction 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 iGoExit: GetNums = MyStrEnd Function

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 FunctionFunction 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

That's great - that will make our finance department very happy.

0

Featured Post

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…

Workbook link problems after copying tabs to a new workbook?
David Miller (dlmille)
Intro
Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…

The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…