Split single cell content into 2 cells

[Miss L]

Hi all,

I m currently working on some data import from text file to excel using excel VBA programming and I m totally new.
Currently I able to convert the text into excel but somehow there's some data not splitting into separate cells.
Please see below example:

The above is an example of excel I obtained after converted from text. But noticed one data in P column A and 1000 does not split. Of course this is just a part of the excel content and an example.
What I need is to search the entire excel sheet to look for those cell with alphabet and numeric and then split the numeric into the next column.
Please see below for the expected result:

Any idea how I can do this?
Thanks

 

[jolivanes]

Change references as required.
This checks from A2 on down.

Sub Maybe()
Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Len(c) > 1 And Left(c, 1) Like "[A-Z]" Then
            With c
                .Offset(, 1).Insert Shift:=xlToRight
                .Offset(, 1).Value = Mid(c, 2)
            End With
        c.Value = Left(c, 1)
        End If
    Next c
End Sub

 

[Miss L]

Change references as required.
This checks from A2 on down.

Sub Maybe()
Dim c As Range
    For Each c In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Len(c) > 1 And Left(c, 1) Like "[A-Z]" Then
            With c
                .Offset(, 1).Insert Shift:=xlToRight
                .Offset(, 1).Value = Mid(c, 2)
            End With
        c.Value = Left(c, 1)
        End If
    Next c
End Sub

Hi, Thanks for the code. I tried modified a bit based on my content.

Dim c As Range
    For Each c In Range("F2:J" & Cells(Rows.Count, 1).End(xlUp).row)
        If Len(c) > 1 And Left(c, 1) Like "m" Then
            With c
                .Offset(, 1).Insert Shift:=xlToRight
                .Offset(, 1).Value = Mid(c, 4)
            End With
        c.Value = Left(c, 3)
        End If
    Next c

This is working for me. But I have another question. If I wanna search based on alphabet more than one character 'max' instead of 'm'
How can I modify accordingly?
Thanks.

 

[jolivanes]

Re: "If I wanna search based on alphabet more than one character 'max' instead of 'm'"
Don't know what you mean by that. Can you explain?

 

[Miss L]

Re: "If I wanna search based on alphabet more than one character 'max' instead of 'm'"
Don't know what you mean by that. Can you explain?

Hi,
Please see below:

I would like to split the max500 to 2 cells

Thanks.

 

[jolivanes]

In Post #1 it shows Column P only.
In Post #5 it does not show any Column.
In Post#3 you changed it to Range("F:J")
Can you let us know which Columns need checking so we don't keep on going around and around because we don't have the proper range.

Don't add to the clutter by quoting whole posts please.

 

[Peter_SSs]

Welcome to the MrExcel board!

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

What I need is to search the entire excel sheet to look for those cell with alphabet and numeric and then split the numeric into the next column.

Give this a try with a copy of your your data.

Sub SplitTextNumbers()
  Dim RX As Object, M As Object
  Dim a As Variant
  Dim i As Long, j As Long
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "(^[A-Za-z]+)([0-9]+$)"
  Application.ScreenUpdating = False
  With ActiveSheet.UsedRange
    a = .Value
    For j = UBound(a, 2) To 1 Step -1
      For i = UBound(a, 1) To 1 Step -1
        If RX.Test(a(i, j)) Then
          Set M = RX.Execute(a(i, j))
          .Cells(i, j + 1).Insert Shift:=xlToRight
          .Cells(i, j).Resize(, 2).Value = Array(M.Item(0).SubMatches(0), M.Item(0).SubMatches(1))
        End If
      Next i
    Next j
  End With
  Application.ScreenUpdating = True
End Sub