Text Deliminate with conditions

[stolie666]

Hi ,and thanks for your help

I currently do a series of cleans of phones number using text deliminate and a series of formulas etc. I would like to automate the process in a macro as much as possible

Data Types;

212345678
312345678
712345678
812345678
412345678

For active cells/ those highlighted; and

Where starts with 2, 3, 7 or 8 (and is 9 characters in length) then put 0 and first character, eg" 02, 03, 07 or 08, in cell to left 1, and remaining 8 characters in original cell or;

Where starts with 4, then add a zero to position 1/ front, and convert to text, data remaining in the same cell

I have read snippets but i am confused. Any out there that can help me?

Thanks in advance

 

[Joe4]

Welcome to the Board!

Try this code:

Sub SplitData()


    Dim cell As Range
    Dim part1 As String
    Dim part2 As String
    Dim output As String
    
    Application.ScreenUpdating = False
    
'   Loop through each cell in selection
    For Each cell In Selection
'       Split entry into two parts
        part1 = Left(cell, 1)
        part2 = Mid(cell, 2, 8)
'       Make determination of what to do
        Select Case part1
            Case "2", "3", "7", "8"
                output = "0" & part1
                cell.Offset(0, -1).NumberFormat = "@"
                cell.Offset(0, -1) = output
                cell = part2
            Case "4"
                output = "0" & cell
                cell.NumberFormat = "@"
                cell = output
        End Select
    Next cell
    
    Application.ScreenUpdating = True
                
End Sub

 

[stolie666]

Amazing! Works perfectly, thankyou so much for your time, i'm slowly teaching myself VBA, but its quite hard for a novice

Again, thankyou

 

[Joe4]

You are welcome!