Split mixed string in excel

Question

I have an intractable problem. There is this huge, long column at work which contains mixed strings with the following format:

ue6584
th45
hur4562243

So it is very irregular, the only regularity is that it starts with letters and ends with numbers. I need to split the strings in each cell so that:

ue6584             —> ue 6584
th45                 —> th 45
hur4562243     —> hur 4562243

So the cell splits into two columns, one column containing the letters only, the other the numbers only. So far, I am thinking this is impossible to do in excel.

Can anyone help please?

Thank you in advance, Dritan

In your final example, do you mean to return `hur 4562243`? – BruceWayne Feb 07 '18 at 22:05 — BruceWayne, Feb 07 '18 at 22:05
Hi Bruce, sorry. Yes exactly. I made the change. – Parmenides Feb 07 '18 at 22:08 — Parmenides, Feb 07 '18 at 22:08

score 3 · Answer 1 · answered Feb 07 '18 at 22:16

3

Or you can use a simple trick with built-in functions:

=LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1) - for string part;
=RIGHT(A1,LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1) - for number part;

answered Feb 07 '18 at 22:16

vlad.lisnyi

325
2
12

...or you can do that! Nice thinking. I was wondering how it'd work with a formula! – BruceWayne Feb 07 '18 at 22:17
This is the right question to ask when you work with data. I do like UDFs, but when it comes up to data sets of thousands of rows and dozens of columns, rather go with simple tricks. Thanks for comment @BruceWayne – vlad.lisnyi Feb 07 '18 at 22:20
I was stuck working on how to iterate through the letters -- didn't think to do the opposite and just get the numbers (like you did with the array). Clever! I'm glad you posted that too, I was about to fall down a rabbit hole for sure... – BruceWayne Feb 07 '18 at 22:22
@BruceWayne have not seen anything yet. I mean use string patterns in VBA. Thanks for the introduction. – vlad.lisnyi Feb 08 '18 at 00:57

BruceWayne · Answer 2 · 2018-02-07T22:22:25.440

~~You will likely need VBA, so~~ I have created a UDF (plus it gave me an excuse to play with RegEx).

First, add the RegEx reference to the VBEditor. See Step 1 from this post for how to do that.

Then add these to a Module in your workbook:

Function return_letters(ByVal target As Range)
Dim regEx As New RegExp

Dim pattern As String: pattern = "[0-9]"
With regEx
    .Global = True
    .MultiLine = False
    .IgnoreCase = False
    .pattern = pattern
End With

If regEx.Test(target) Then
    return_letters = (regEx.Replace(target, ""))
End If

End Function

Function return_numbers(ByVal target As Range)
Dim regEx As New RegExp
Dim pattern As String: pattern = "[a-zA-Z]"
With regEx
    .Global = True
    .MultiLine = False
    .IgnoreCase = False
    .pattern = pattern
End With

If regEx.Test(target) Then
    return_numbers = (regEx.Replace(target, ""))
End If

End Function

Finally, just call each function:

Split mixed string in excel

2 Answers2