Split alphanumerical string when encounter alphabetic character -Excel 1

[imme]

Hello Friends,

I need help with splitting an alphanumerical string. I am using Excel to manage this data but if you have any better suggestion, please let me know.

I have a string starting with numerical part which has at the end some soft of alphabetical character followed by 1-9 numerical number. I want to split string when it encounters alphabetic character.

I HAVE:
Column 1

4550368
458424377A0
5084532B4

I WANT:
Column 1 Column 2

4550368
458424377 A0
5084532 B4

As you have observed here, I have varied length for the string.

Please advice. Thanks in advance.

 

[bluedragon2]

=IF(CODE(RIGHT(A1,2))<58,A1,VALUE(LEFT(A1,LEN(A1)-2)))



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

You can use:

=IF(CODE(RIGHT(A1,2))<58,"",RIGHT(A1,2))

to get the last 2 characters if they meet the condition.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[johnnygeo]

May be overkill, but I have a custom function that accomplishes almost exactly this.

Usage: =SplitAlphaNum(myString, lookForAlpha, StopAfterChange)
myString is any (?) text string
lookforAlpha = TRUE to return the alpha portion, FALSE to return the numeric portion
StopAfterChange = TRUE if you only want the first contiguous string of alpha/numeric characters, FALSE if you want to concatenate all such characters.

Function SplitAlphaNum(myString As String, Optional lookForAlpha As Boolean = True, Optional StopAfterChange As Boolean = True)

    myOutput = ""
    KeepLooking = True
    
    For I = 1 To Len(myString)
        myChar = Mid(myString, I, 1)
        'MsgBox "I: " & I & ", myChar: " & myChar & ", isnum: " & IsNumeric(myChar)
        If lookForAlpha = True Then
            If Not IsNumeric(myChar) Then
                myOutput = myOutput & myChar
                If StopAfterChange = True Then KeepLooking = False
            Else
                If KeepLooking = False Then
                    Exit For
                End If
            End If
        Else
            If IsNumeric(myChar) Then
                myOutput = myOutput & myChar
                If StopAfterChange = True Then KeepLooking = False
            Else
                If KeepLooking = False Then
                    Exit For
                End If
            End If
        End If
    Next I

    SplitAlphaNum = myOutput
End Function

use "123abc456def" as myString and play with the parameters to see what they do.

for your specific example, assuming your input data starts in A1, I would put the formula =SplitAlphaNum(A1,FALSE,TRUE) in B1. Then I would put =RIGHT(A1,LEN(A1)-LEN(B1)) in cell C1.

rgds,
Johnny Geo

 

[imme]

Thanks bluedragon2 and johnnygeo.

Bluedragon2 -

As I have to capture last 0-2 character(s) due to different data strings, I have modified your function so it can first count position of alphabetic character and then based on that split the string.
Thanks a lot for giving me idea.

Here is updated function.

First Part:
=LEFT(B2,LEN(B2)-(IF(CODE((RIGHT(B2,2)))<58,IF((CODE(RIGHT(B2,1)))<58,0,1),2)))

Second Part:
=RIGHT(B2,IF(CODE((RIGHT(B2,2)))<58,IF((CODE(RIGHT(B2,1)))<58,0,1),2))

If you want to suggest any better way, please do it.


johnnygeo -

I am not familiar with custom functions and how to use it in excel. If you can point any tutorial to show how to use such scripts, please provide link. Thanks a lot for your time.

 

[johnnygeo]

http://www.tek-tips.com/faqs.cfm?spid=68&sfid=1271