Separate Alpha and Numberic Characters

[Rocky0201]

Hi...

I have a string that contains a value of Alpha and Numberic characters. How best can I break the Alpha characters from the Numberic Characters?

Example:

TmpValue ="Test25"

Needed Result"

Array(0) would = Test
Array(1) would = 25

Thanks in advance for the help.

 

[Rick Rothstein]

If your "number" can have leading zeroes, then you will need it to be returned as text in order to keep any leading zeroes that may be present. If that is the case, then give these formulas a try...

B2: =MID(A2,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")),LEN(A2))

C2: =SUBSTITUTE(A2,C2,"")

 

[Rocky0201]

Thank you all for the help!!!

Question; if I were to use the SplitCell function in my VBA code, Would I create the function in a Module and use it in my code as:

SplitCell("Test1234")

I tried doing this and when I return to my code from SplitCell, I was expecting to see SplitCell built as an Array:

SplitCell(0) would be Test
SplitCell(1) would be 1234

I obvioulsy am not using the code function correctly or I am lookinig at the wrong place for the results. I placed my breakpoint at my SplitCell function and stepped through the function. CellVal did contain "Test1234". The code did go through the test and executed:

Set Match = .Execute(CellVal)(0)
SplitCell = Array(Match.submatches(0), Match.submatches(1)

However, when I came out of the function, I looked at SplitCell using watch with the result being:

<Expression not defined in context> in the Value column
Empty in the Type column

Again, thank you so much for the help.

 

[GTO]

Thank you all for the help!!!

Question; if I were to use the SplitCell function in my VBA code, Would I create the function in a Module and use it in my code as:

SplitCell("Test1234")

Hi Rocky,

I am afraid that at least I was reading into your question a bit, as for whatever reason, I was expecting a string from a cell.

In answer to the above, not exactly, at least not as you show it. For the Function to return something to the celling procedure either an arg (or args) passed must be passed via an existing variable in the calling procedure, or more commonly, the return of the Function must be assigned to a variable to be useful. That may have sounded a bit abstract, but try this and see if it makes sense.

Sub example()
Dim MyVar
    
    MyVar = SplitCell("Test0123")
    MsgBox ">" & MyVar(0) & "<"
    MsgBox ">" & MyVar(1) & "<"
    'OR
    MsgBox ">" & SplitCell("Test0123")(0) & "<"
    MsgBox ">" & SplitCell("Test0123")(1) & "<"
    
End Sub

In the first part, we use the more common method of assigning the return from the Function, and then just work with the resultant variable. In the second, we actually just return one element of the function, which might be useful if we only really needed one bit from it.

...I tried doing this and when I return to my code from SplitCell, I was expecting to see SplitCell built as an Array:

SplitCell(0) would be Test
SplitCell(1) would be 1234

I obvioulsy am not using the code function correctly or I am lookinig at the wrong place for the results. I placed my breakpoint at my SplitCell function and stepped through the function. CellVal did contain "Test1234". The code did go through the test and executed:

Set Match = .Execute(CellVal)(0)
SplitCell = Array(Match.submatches(0), Match.submatches(1)

However, when I came out of the function, I looked at SplitCell using watch with the result being:

<EXPRESSION context in defined not>in the Value column
Empty in the Type column

Again, thank you so much for the help.

As shown, SplitCell does return a 2-element/1D array. Not sure exactly what you used as a watch, but if you open the Locals window, right after
SplitCell = Array(Match.submatches(0), Match.submatches(1) executes, you should be able to expand it in locals.

Does that help?

Mark

 

[Rocky0201]

Excellant Mark!!! this is exactly what I have been looking for. Thank you so much!!!

 

[Rick Rothstein]

In case you might be interested, here is the non-RegExp equivalent of the SplitCell function (can be used as an array-entered UDF also)...

Function SplitCell(S As String) As Variant
  Dim X As Long, Parts() As String
  ReDim Parts(1 To 2)
  For X = Len(" " & S) To 1 Step -1
    If Mid(" " & S, X, 1) Like "[!0-9]" Then
      Parts(1) = Left(S, X - 1)
      Parts(2) = Mid(S, X)
      SplitCell = Parts
      Exit For
    End If
  Next
End Function

Note that the ReDim statement makes the returned array one-based, but you can change it to make the function return a zero-based array if you want.

Also, the functionality can be split out into two separate, focused functions as opposed to a single array generating function (the function name tells it functionality)...

Function LeadingText(S As String) As String
  Dim X As Long
  For X = 1 To Len(S & " ") + 1
    If Mid(S & " ", X, 1) Like "[!A-Za-z]" Then
      LeadingText = Left(S, X - 1)
      Exit For
    End If
  Next
End Function
 
Function TrailingNumber(S As String) As Variant
  Dim X As Long
  For X = Len(" " & S) To 1 Step -1
    If Mid(" " & S, X, 1) Like "[!0-9]" Then
      TrailingNumber = Mid(S, X)
      Exit For
    End If
  Next
End Function

 

[GTO]

Excellant Mark!!! this is exactly what I have been looking for. Thank you so much!!!

Hi Rocky,

Happy to be of what help I could be, but I sure hope you spot this later and try Rick's. It is about 2.4 times faster!

I hope you will not mind me poking in a bit of a question to the others, but it is always nice when an opportunity to understand something a bit better presents itself.


All ya'll,

Reference the formula solutions:

Okay - I knew there'd be a better way, but what a trouncing! Ow, ow, ouch! (I think I have permanent scarring)

Seriously, I wanted to take a moment to thank you all. While of course the answers are for the OP, formulas are more a mystery for me than code, and this (Aladin's, Biff's, and Rick's ansers) was a nice chance to maybe get a better grasp...

If one of you would not mind, I would like to confirm (or be corrected as to) my understanding as to the steps of, in this case, Aladin's formula.

Let us say that A2 contains 'Test5123' as its value/text.

To return the number portion (as a string), Mr. Akyurek uses:

=REPLACE(A2,1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789"))-1,"")

Now at the core of the formula is:

=FIND({0,1,2,3,4,5,6,7,8,9},A2&"0123456789")

For reasons I do not totally understand, no CSE confirmation is needed, but this, given our test value, returns an array of:

{9,6,7,8,13,5,15,16,17,18}

I believe I do understand the return array, as first, we look for '0', which in this case, will be at position 9, the first character in the appended string. Is my thinking is correct, in that we include the appended string really only to prevent errors from searching for characters that do not exist?

From there, 6 is returned next, as '1' is found at position 6 and so on...

Now, MIN() can return a value, for as mentioned, it will not be finding any errors in the array returned by FIND(), due to the appended number string. Thus, MIN() returns the first position of a (any) number in the test value.

From there, the formula uses the result of MIN()-1 to instruct how many characters are to be replaced with an empty string.

Presuming that is all correct, is there any type of general rule or logic as to when we need to enter (a single cell formula) via CSE?

I hope all that was sensible, and again, thank you

Mark