Split content of a cell into different cells with vba

[kelly mort]

I have in column D strings like

A1B1C1D1E1 or A5B1C3D1E4 etc

There can be blanks too. There length will always be even if not blank.

Which means we can have from 2 characters up to 10 characters.

So from the above given example if cell D2 is having A5B1C3D1E4, then I am splitting it and filling from I2 to M2 with

5, 1, 3, 1, 4

Then we repeat that to last used row in D.

So if length of the string is not up to 10, then the remaining cells in col I to M is filled with blank.

I hope this is possible .

Thanks in advance

 

[DanteAmor]

For what I am running this test on, col D has no letters.
It's all numbers.
The code worked when I have them with letters as shown in your current post.

So this macro is not for your original requirement:

have in column D strings like
A1B1C1D1E1 or A5B1C3D1E4 etc

If you have another requirement, I'll gladly review it, but you should put what the considerations are now.

 

[Rick Rothstein]

@kelly mort,

Just wondering...

1) Did the code I posted in Message #9 work for you or not?

2) Did you see the explanation of my code that you asked for in Message #15?

I meant Message #7 , not #9.

For what I am running this test on, col D has no letters.

It's all numbers.

The code worked when I have them with letters as shown in your current post.

Like DanteAmor. (Message #21).. I too would like to see what your new data looks like and what result you want from it.

 

[kelly mort]

@kelly mort,

Just wondering...

1) Did the code I posted in Message #9 work for you or not?

2) Did you see the explanation of my code that you asked for in Message #15 ?

Yes to both questions.

I responded to question 1 already.

And sorry for not responding to question lately.


I am still on @DanteAmor post because I wanna understand it too.

Regards

Edit:

Your code and @Fluff own run smoothly no matter what

 

[DanteAmor]

Yes to both questions.
I responded to question 1 already.
And sorry for not responding to question lately.
I am still on @DanteAmor post because I wanna understand it too.
Regards
Edit:
Your code and @Fluff own run smoothly no matter what

What this does is replace the letters with a space. if you add the numbers it also replaces them by space

.Pattern = "[[COLOR=#0000ff]A-Za-z[/COLOR]]"
            .Global = True
            c.Offset(0, 5).Value = .[COLOR=#ff0000]Replace[/COLOR](c.Value, [COLOR=#0000ff]" "[/COLOR])

 

[kelly mort]

So this macro is not for your original requirement:



If you have another requirement, I'll gladly review it, but you should put what the considerations are now.

The new requirement is:

Having either all numbers in col D or both numbers and letters .

Example 1122334455 or A1B2C3D4E5 Etc

 

[DanteAmor]

The new requirement is:

Having either all numbers in col D or both numbers and letters .

Example 1122334455 or A1B2C3D4E5 Etc

What is the expected result?

 

[kelly mort]

What is the expected result?

The result is just like the initial one:

With A1B2C3D4E5, it should output 1 2 3 4 5

 

[Rick Rothstein]

The result is just like the initial one:

With A1B2C3D4E5, it should output 1 2 3 4 5

Do you have cells that are all numbers like 1234567890? If so, what result do you want from them?

 

[kelly mort]

Do you have cells that are all numbers like 1234567890? If so, what result do you want from them?

That should be 2 4 6 8 0

And your version of the code is doing that for now

 

[Rick Rothstein]

That should be 2 4 6 8 0

And your version of the code is doing that for now

Great... good to know. I was mainly asking because DanteAmor asked and you seemed to sidestep that part of his question... he'll want to know that for when he comes back on line.