Special Dataconvert

[MarcMermans]

Hello All,


I need some help to convert a list of 40000 items to a specific format in Excel.
I have a colum with the following data

LFO1066A
LFTF105
PTXTAA103


They need to be converted to the following format


[TABLE="width: 500"]
<TBODY>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]LFO1066A
[/TD]
[TD]L
[/TD]
[TD]FO
[/TD]
[TD]1066
[/TD]
[TD]/
[/TD]
[TD]A
[/TD]
[/TR]
[TR]
[TD]LFTF105
[/TD]
[TD]L
[/TD]
[TD]FTF
[/TD]
[TD]0105
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PTXTAA103
[/TD]
[TD]P
[/TD]
[TD]TXTAA
[/TD]
[TD]0103
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

Column B always a lenght of 1
Column C max lenght of 5
Column D always lenght of 4
Column E / if the last character of Column A is alfanummeric
Columm F the alfanummeric character

Can someone give me some hints to solve the problem with VBA.

Thankx in advance.

 

[s hal]

Should the B column for the last example be P instead of L?

Edit: and to really solve this, we would need to know more about your data set. It looks like you have a combined Alpha-numeric of random lengths, but not enough rules governing how the letters and numbers appear

 

[MarcMermans]

Should the B column for the last example be P instead of L?

Yes thats correct.

 

[s hal]

Marc, Please see my edit above and comment on whether you know any additional information - the more you know about the data, the more feasible it is to break apart the string into its components. For instance, if you know the first position is always available, that's a start that we don't have right now. The next grouping can be X-5 (we don't know X) length, but we don't know what it can be. Perhaps there is a field of some sort that can be searched for, which would make the next field (which appears to possibly be solely numeric) more easily distinguishable.

I think the last position is probably the easiest assuming the second to last is 100% numeric..

 

[MickG]

Try this:-
Asumed your data starts in "A1"

[COLOR="Navy"]Sub[/COLOR] MG17Apr38
[COLOR="Navy"]Dim[/COLOR] Rng         [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn          [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] num         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] eTxt        [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] n           [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Txt         [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]For[/COLOR] n = 1 To Len(Dn)
        [COLOR="Navy"]If[/COLOR] Mid(Dn, n, 1) Like "[a-zA-Z]" [COLOR="Navy"]Then[/COLOR]
             [COLOR="Navy"]If[/COLOR] n = Len(Dn) [COLOR="Navy"]Then[/COLOR]
                eTxt = Mid(Dn, n, 1)
            [COLOR="Navy"]Else[/COLOR]
                Txt = Txt + Mid(Dn, n, 1)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]ElseIf[/COLOR] Mid(Dn, n, 1) Like "[0-9]" [COLOR="Navy"]Then[/COLOR]
            num = num & Mid(Dn, n, 1)
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR] n
Dn.Offset(, 1) = Left(Txt, 1)
Dn.Offset(, 2) = Right(Txt, Len(Txt) - 1)
Dn.Offset(, 3).NumberFormat = "0000"
Dn.Offset(, 3) = num
    [COLOR="Navy"]If[/COLOR] Not eTxt = "" [COLOR="Navy"]Then[/COLOR]
        Dn.Offset(, 4) = "/"
        Dn.Offset(, 5) = eTxt
    [COLOR="Navy"]End[/COLOR] If
Txt = "": num = "": eTxt = ""
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick