VB Code to split data

[Oszie]

HI All

I hoping someone can help me with this.

I have data only in Column A, its Alphanumeric and numeric numbers.

Example of Data
992G73
992G83
992H11
992H86
992I18
992I24
992I41
992I47
993D81
993I86
995E15


What I need is this data to be split like this.

992|G|73
992|G|83
992|H|11
992|H|86
992|I|18
992|I|24
992|I|41
992|I|47
993|D|81
993|I|86
995|E|15

Can anyone help me?

[ragulduy]

Is the input data always 3 numbers, one letter two numbers? If so use this formula:
=left(A1,3)&"|"&mid(A1,4,1)&"|"&right(A1,2)

note, you can do the same thing in vba:

Please Login or Register  to view this content.

[AlphaFrog]

Is the pattern always 3 digits, one letter, two digits? If yes, you could use the Text-to-Columns feature; fixed width option.

EDIT: I interpreted the bars to be cells. Split the data into three columns. Is that what you want?

[XOR LX]

Or:

Please Login or Register  to view this content.

Regards

[Oszie]

Hi

Thank you for the response.

No, there could be.

4434B52
1000C52

[XOR LX]

"Is the pattern always 3 digits, one letter, two digits?"

"No, there could be.

4434B52
1000C52"

Then why on earth have you given 11 examples, all of which are of that format?

Regards

[Oszie]

Hi XOR LX

My apologies, In my rush, I just copied the data and pasted it in, without look at the bigger picture. I looked at all the data the only number of digits that change is the first string, then its always one Alpha and then 2 digits.

[Fotis1991]

Using formulas.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

The second one is an Array formula.

Please Login or Register  to view this content.

You can record these as macro if you like.

-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.

[ragulduy]

Maybe you need some additional examples covering the possibilities, if those are the only two variations then you could use:
=left(A1,if(len(A1=6),3,4))&"|"&mid(A1,=if(len(A1=6),4,5),1)&"|"&right(A1,2)

[jindon]

VBA

Please Login or Register  to view this content.

[XOR LX]

Or this, perhaps:

Please Login or Register  to view this content.

Regards

[Oszie]

HI Fotis1991

these formula does not seem to work, properly please see below results

004E22 400 #VALUE! 22
004E24 400 #VALUE! 24
004E25 400 #VALUE! 25
004E26 400 #VALUE! 26
004E28 400 #VALUE! 28
004E31 4000 #VALUE! 31
004E33 4000 #VALUE! 33
004E34 4000 #VALUE! 34
004E37 4000 #VALUE! 37
004E38 4000 #VALUE! 38
004E42 40000 #VALUE! 42
004E46 40000 #VALUE! 46
004E48 40000 #VALUE! 48
004E51 400000 #VALUE! 51
004E52 400000 #VALUE! 52

[Oszie]

HI jindon and XOR LX

I'm very new at this, and I do apologize for my ignorance on the matter. its working great, exactly as I explained in the illustration, but I meant with the pipe sign to represent a new column.

A B C
992|G|73
992|G|83

[ragulduy]

you could add something like:

Please Login or Register  to view this content.

to the end of either XOR LX or jindon's code.

[jindon]

HI jindon and XOR LX

I'm very new at this, and I do apologize for my ignorance on the matter. its working great, exactly as I explained in the illustration, but I meant with the pipe sign to represent a new column.

A B C
992|G|73
992|G|83

Change to

Please Login or Register  to view this content.

[XOR LX]

Argghh!!!

Only kidding! No worries:

Please Login or Register  to view this content.

Regards

[Fotis1991]

Please Login or Register  to view this content.

[jindon]

Oszie
Thanks for the rep and if the problem is solved, please mark the thread as "SOLVED".

OOps, just marked so...