SEPARATING ALPHANUMERIC DATA WHEN PATTERN IS IRREGULAR

[tyronemeister]

Is there a way to automatically separate numbers and letters in cells when the number of letters and/or numbers varies, preferably with vba? That is, separate them to different columns.

There's no delimiter and the width is not fixed, so the text to columns feature is not useful without sorting the information first. Apparently, isnumber cannot be used to because numbers are recognized as text when concatenated with letters.

Here's an example of what I want to do:

Before separating the data:

12ABB
1ABB
12AB

After separating:

Col1    Col2

12       ABB
1        ABB
12       AB

Thanks.

 

[Norie]

Are the numbers always at the start?

Sub Seperate()
    For Each c In Range("A1:A10")
    
        c.Offset(, 1) = Val(c.Value)
        c.Offset(, 2) = Mid(c.Value, Len(c.Offset(, 1)) + 1)
        
    Next c
End Sub

 

[Greg Truby]

<ul>[*]=regexpsubstitute(A1,"[A-Z a-z]","")[*]=regexpsubstitute(A1,"[0-9]","")[/list] Where the function regexpsubstitute is taken from Tushar's site.

 

[tyronemeister]

Both solutions work.

Thanks!

 

[Greg Truby]

Yes, but you may wish to make note of the difference in output should the input ever stray from the format you describe. Compare the results of each when inputs are things like<ul>[*]1AB2DE3[*]123ABC123[*]GHI789[*]XYZ[*]123[/list]I cannot speculate as to which solution would yield more desireable results for you (or indeed perhaps neither would). But I would think that from your POV it would be worth noting.