Extracting Letter/Number from Alphanumeric string (4 different times from same string)

I tried to look this up but wasn't able to find a solution on how to do this (or maybe I did and didn't realize I had). I am an Excel novice that for the most part can find what I am looking for with Google searches. Here is some background on what I am trying to accomplish and the point that has me stuck:

I needed to come up with a way to get every combination of 4 columns (16 things in each column). I thought I had it solved perfectly using Microsoft Query since it sorted the combinations in 4 different cells that I could copy and paste into excel. I then realized that the query was stopping at 16,xxx cells each time. I then looked on here and got some VBA code to do the trick but it stores all the combinations into 1 cell......hence my problem.

Ex:

A1 B1 C1 D1 is taken and thrown into a cell as this A1B1C1D1. So now I have 65,536 cells that I need to break up into 4 cells with all the combinations. Can this be done with a formula or do I need to search for VBA coding (which I know nothing about)?

Thank you in advance for any help. I am not looking to be spoon fed the answer as I just want to be nudged in the right track and I will do the work of looking it up (will probably be back with more questions though ).

Originally Posted by Jakobshavn

very easy if your data has a regular pattern.

For example A1B1C1D1 is 4 groups X 2 characters per group.

Is all your data like this??

No it isn't. Because it gets further down it could look like this since each column is 16 long ...... A1B16C16D16. The biggest any of the 4 groups will be is 3 characters though. I have each of the 4 columns going from a "Letter" with a number 1 through 16 after them. I am guessing I have to learn more about Left, Right, Len, Find......but wasn't really sure. Didn't know if it was going to require a few If statements.

Thanks for your help though into looking at it.

OK I am messing around with the formula you posted above. I have at least realized now the 2* part tells it to move every 2 characters by messing around and changing it to a 3. I just need to figure out how to change it around to read if it is 2 digits after a letter or 3 as it reads across all 4 letters 65,000 times. I am sure I will be back later with more questions.

Thank you

Originally Posted by Jakobshavn

Here it is:

=IF(LEN($A1)=8,MID($A1,2*COLUMNS($A:A)-1,2),MID($A1,3*COLUMNS($A:A)-2,3))

I just got to office and saw your latest post. That worked great. Still don't fully understand it all but thank you so much for the help.

Ok I just found a few glitches in it. An example of one line is this:

t10u1v11w2 then split up like this: t10 u1v 11w 2

It is completely random on the 3 or 2 length as it filters through all 65,536 combinations. Since each column has 9 with 2 character length and 7 with 3. The formula above worked perfectly when the first 3 letters had 2 numbers after but problems arose when the 3rd set dropped to 2 character length.

Originally Posted by Jakobshavn

The equation was designed to handle inputs of either 8 or 12 characters.

You would need to specify how a string of length 9,10,11 characters should be split.

Basically what I am trying to do is have it split by Letter and then number immediately after it. Here is what is in column A, B, C, & D:

a1 b1 c1 d1
a2 b2 c2 d2
a3 b3 c3 d3
a4 b4 c4 d4
a5 b5 c5 d5
a6 b6 c6 d6
a7 b7 c7 d7
a8 b8 c8 d8
a9 b9 c9 d9
a10 b10 c10 d10
a11 b11 c11 d11
a12 b12 c12 d12
a13 b13 c13 d13
a14 b14 c14 d14
a15 b15 c15 d15
a16 b16 c16 d16

So the VBA code as went through and did every combination of all 4 columns. The problem is that it is stored each combination into one cell like this:

a1b1c1d16
a1b1c1d15
a1b1c1d14
......etc
a1b1c16d1
a1b1c15d1
a1b1c14d1

So from above I would like the 4 groups broke up into their own cell so on the bottom example it would be a1, b1, c14, & d1. That is why I have struggled so much with this is that I am trying to have it read across and see the letter and then determine if it is 1 digit or 2 digit number, and then repeat with the next letter all the way out to the 4th letter. Sorry for not explaining it better at top.

Thanks

That did the trick. Thank you so much for you help.