Paste info from one cell to many cells.

[angelous]

Hello.

I am really a someone who do not know anything about VBA, but i need expert help to make a macro what coult make my work easyer. I hope someone can help me. I searched this forum, but didn't find or understand.

I need info how can my excel sort text from one cell to 6 diffrent cell.
I know how to make macro to find text what is needed to sort, because the text has similarity:

text for sort:
first sheet
-----cell 1---------
AAAAA1AA2A3ABA4A5C67AAAAAAAAAA

text sorted:
second sheet
cell 1 - cell 2 - cell 3 - cell 4 - cell 5 - cell 6
1 2 3 4 5C6 7

No 1 has the AAAAA and the AA as markings
No 2 include's also A's and C's
No 3 and 4 has B as the marking and also second A from B to right and left
No 5 and 6 has C and AAAAAAAAAA as markings. C = . and 6 = 2 digit no.
No 7 = ! The 7 is something what is can be and can not be in text.

Text temp for sorting:
sa431bkj3142k3j1b235b1642kj16b
1bj46b1j6b1j346jb1b364h1364hb1
AAAAA1AA2A3ABA4A5C67AAAAAAAAAA
AAAAA1AA2A3ABA4A5C67AAAAAAAAAA
k6j1o3i46j1i3kj4po61j34o6jo134
AAAAA1AA2A3ABA4A5C67AAAAAAAAAA
1mk34lk6j1k3j46143kl6143j61j13
AAAAA1AA2A3ABA4A5C67AAAAAAAAAA
143o6ko1k436o1k34o61k3o4k6k1o3

 

[Gavona]

I cannot really see the structure that you describe in your test data. Please can you check your description and also post the results you would expect from the test data.

No2 for example actually appears to be delimited by As.

For starters select your test data then
Data,TextToColumns
Delimited [next]
Other, A treat consecutive delimiters as one [next]

If you can change all your delimiters to a standard character then Text to Columns can probably do MOST OF the job for you. To do this have a look at the Substitute function. For example if your test data in in cell A1 then the following will start you off.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A4,"AAAAA","|",1),"AA","|",1),"A","|",1),"ABA","|",1),"A","|",1),"AAAAAAAAAA","|",1)

Results in 
|1|2|3|4|5C67|

to which you apply text to columns
then apply text to columns to the last column using the fixed width option.

If I was more confident I understood your problem and test data then the approach could probably be simplified. #I suggest that you build the formula up one Substitute at a time so you can understand what is going on.


Gavin