# Special Dataconvert

#### MarcMermans

##### New Member
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

 A B C D E F LFO1066A L FO 1066 / A LFTF105 L FTF 0105 PTXTAA103 P TXTAA 0103

<TBODY>
</TBODY>

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.

Last edited:

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

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

Yes thats correct.

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..

Try this:-
Asumed your data starts in "A1"
Code:
``````[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

Replies
1
Views
131
Replies
0
Views
136
Replies
3
Views
180
Replies
8
Views
250
Replies
3
Views
455

1,203,681
Messages
6,056,712
Members
444,886
Latest member
peishin98

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back