VBA to split text to multiple columns

[Cunning]

Hi,
I've been trying to assign a macro to sort some data.

In column A I have some text data e.g. "Fe465 - TRN" or "Fe347/1 - YRH"

What I'd like to be able to do is split this so that once the macro has run I'd have in A: Fe in B: 465 or 347/1 and in C: TRN or YRH.

The data runs from A3 down.

TIA

 

[Cunning]

Sorry, I should add that it's not always 3 letters at the end. It can be up to 15... i.e. instead of TRN it can be "training/work"

 

[MrKowz]

Will it always be 2 characters at the beginning that you put into Col A?

Might want to give us several examples of your data to work with so we can make sure to tailor the macro properly.

 

[Cunning]

It's always 2 letters at the start followed by either 3 numbers or 3 numbers with a slash and another number i.e. 347/1 648/8 and then it is a hyphen followed by the description.

Fe384/2 - training
Be348 - work
Tr494/2 - work
Be349 - training
Fe145 - TRN
Rt494/3 - Yearly

etc etc.

 

[MrKowz]

Try:

Public Sub SplitColA()
Dim i   As Long, _
    LR  As Long, _
    str As String
LR = Range("A" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = 1 To LR
    str = Range("A" & i).Value
    Range("A" & i).Value = Left$(str, 2)
    Range("B" & i).Value = Mid$(str, 3, InStr(str, " - ") - 2)
    Range("C" & i).Value = Right$(str, Len(str) - InStr(str, " - ") - 2)
Next i
Application.ScreenUpdating = True
End Sub

 

[Cunning]

That's great, but it does fall down if there's a title block. Is there an easy way to set the range to intersect at row 3 instead of all of column A?

 

[MrKowz]

Sure is:

Public Sub SplitColA()
Dim i   As Long, _
    LR  As Long, _
    str As String
LR = Range("A" & Rows.Count).End(xlUp).row
Application.ScreenUpdating = False
For i = [B][COLOR=red]3[/COLOR][/B] To LR
    str = Range("A" & i).Value
    Range("B" & i).Value = Mid$(str, InStr(str, "Country") + 9, InStr(str, "http://") - InStr(str, "Country") - 9)
    Range("C" & i).Value = Right$(str, Len(str) - InStr(str, "http://") + 1)
Next i
Application.ScreenUpdating = True
End Sub

 

[Cunning]

That's perfect. If I did have some data within the mix that had 3 letters at the start would that be a difficult thing to take into account too?