Excel split cell into 2 separate cells by letters/digits

Question

Let's say you have 2 worksheets, in the first worksheet you have lots of different data. In D column, you have a number (in bold). First part of the number are 2 or 3 letters and then a couple of digits after it (number of digits can vary), for example HTG5342355 or PO23455, not every cell in column D has such number, it can be in D3 but then it can be in D6, D7, D20 ... (it's always in column D though)

How would it be possible to copy the first 2 or 3 letters into the second worksheet as one cell and the digits as another cell right next to it.

Edit:

Just wanted to add some info to this question:

In column D, there is also other data, so it looks something like this:

**HTG5342355**
another text
**PO23455**
**BT3452342**
something
something else
**NN23355**

Only the numbers that are in bold need to be split, the other stuff is not relevant for another worksheet

Answer 1

With your data in Sheet2!D1, put the following formulas where you want to return the parts:

For the letters at the beginning:

=LEFT(Sheet2!D1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},Sheet2!D1&"0123456789"))-1)

For the digits at the end:

=MID(Sheet2!D1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},Sheet2!D1&"0123456789")),99)

Answer 2

Dim cellvalue as string, textlength as integer, foundnumber as boolean, _
  y as integer, x as integer
with thisworkbook.sheet1
  do until .cells(y,4).value=vbnullstring
    cellvalue=.cells(y,4).value
    textlength=len(cellvalue)
    if textlength > 1 then
      foundnumber=false
      for x= 2 to textlength
        if foundnumber=false then
          if isnumber(mid(cellvalue,x,1)) then
            foundnumber=true
            thisworkbook.sheet2.cells(y,4).value=left(cellvalue,x-1)
            thisworkbook.sheet2.cells(y,5).value=mid(cellvalue,x)
          end if
        end if
      next x
    end if
  y=y+1
  loop
end with

I believe this will work, though I'm a little strapped for time and haven't tested it. It probably needs some more tests to make sure that the data in the cell is the sort that you want to copy, but hopefully it will get you started. I can come back and comment it later.

Answer 3

With formulae (copied down to suit):

=IF(ISNUMBER(VALUE(MID(Sheet1!$D1,3,1))),LEFT(Sheet1!$D1,2),LEFT(Sheet1!$D1,3))  

.

=IF(ISNUMBER(VALUE(MID(Sheet1!$D1,3,1))),MID(Sheet1!$D1,3,LEN($D1)),MID(Sheet1!$D1,4,LEN(D$1)))  

Answer 4

You could write a big-and-ugly complicated formula, but, although this might be overkill, I would use regular expressions. Read this question and its answers for further reference.

VBA supports regular expressions, but you have to enable them in your project:

1. Open the VBA editor
2. In the Tools menu, click on References
3. Look for Microsoft VBScript Regular Expressions 5.5 and enable the check mark.

Now, create a new module and write some code. Something like this:

function Split_Letters_And_Digits(input_str as String) as String()
    dim re as RegExp
    set re = new RegExp
    dim ans(2) as String
    with re
        .global = True
        .ignoreCase = True
        .multiline = False
        .pattern = "([A-Za-z]{2,3})([0-9]*)"
        ' This pattern will match 2 or 3 upper or lower case letters
        ' and any number of digits after that.
        ' Each group is enclosed in parentheses; this will allow you
        ' to get each group separatedly
    End With
    ' Check if the input string matches the pattern
    if re.test(input_str) then
        ans(1) = re.replace(input_str, "$1")
        ans(2) = re.replace(input_str, "$2")
        ' Those "$1" and "$2" are special tokens that enable you to get
        ' the first and second piece of the pattern; that's the reason
        ' for those parentheses in the pattern
    else
        ' If the input doesn't match the pattern, exit the function
        exit function
    end if
    Split_Letters_And_Digits = ans
end function

This is an array function. To use it, select a two cell range, and write =Split_Letters_And_Digits(D3) and press Ctrl+Shift+Enter.

You can write Regular Expressions to match much more complicated patterns (patterns that you may be unable to split using plain formulas), so it's worth to learn how to use them.

Hope this helps you