put elements of columuns A & D into column E

[wali]

Hallo,

i have to columns in my worksheet: Column A and Column D
Both these columns contain elements which are separated either by a number followed by dot "1." , "2." , "3.".... or they are separated by comma "," by semicolon ";" by ":" or by dot "."
For example:

Column A....................................................................................................................Column D

1. ṭṣ̣̌́ṣ̌́ā1, ṭṣ̣̌́ṣ̌́ā 2, ṭṣ̣̌́ṣ̌́ā 3 2. áṣ̣̌́́ṣ̣̌́š́ə́čṣ̌́1, áṣ̣̌́́ṣ̣̌́š́ə́čṣ̌́, áṣ̣̌́́ṣ̣̌́ṣ̣̌́š́ə́čṣ̌́ 3. š́ə́ɪ́ṣ̌́ā ṣ̣̌́ṣ̣̌́́č, š́ə́ɪ́ṣ̌́ā ṣ̣̌́ṣ̣̌́́č ā́ ................... 1. word1, word 2, word 3 2. another1, another, anoother 3. third one, third one b
ṭṣ̣̌́ṣ̌́āṣ̣̌́ṣ̣̌́́č, ṭṣ̣̌́ṣ̌́āš́ṭṣ̣̌́; ṭṣ̣̌́ṣ̌́ā š́ə́ṣ̌́čč: ṭṣ̣̌́ṣ̌́ā ḍṣ̣̌́šṣ̌́. Wṣ̣̌́ṣ̌́āḍɪ́ṣč, ṭṣ̣̌́ṣ̌́āš̄́ɪ́ū́ ................................................. wordone, wordtwo; word three: word four. Wordfive, wordsix
ṭṣ̣̌́ṣ̌́ā1 ........................................................................................................................ word100
ṭṣ̣̌́ṣ̌́ā .......................................................................................................................... word200


I need to combine both columns A and D into Column E as:

1. word1 (ṭṣ̣̌́ṣ̌́ā1), word 2 (ṭṣ̣̌́ṣ̌́ā 2), word 3 (ṭṣ̣̌́ṣ̌́ā 3) 2. another1 ( áṣ̣̌́́ṣ̣̌́š́ə́čṣ̌́1), another (áṣ̣̌́́ṣ̣̌́š́ə́čṣ̌́), anoother (áṣ̣̌́́ṣ̣̌́ṣ̣̌́š́ə́čṣ̌́) 3. third one (š́ə́ɪ́ṣ̌́ā ṣ̣̌́ṣ̣̌́́č), third one b (š́ə́ɪ́ṣ̌́ā ṣ̣̌́ṣ̣̌́́č ā́)
wordone (ṭṣ̣̌́ṣ̌́āṣ̣̌́ṣ̣̌́́č), wordtwo (ṭṣ̣̌́ṣ̌́āš́ṭṣ̣̌́); word three (ṭṣ̣̌́ṣ̌́ā š́ə́ṣ̌́čč): word four (ṭṣ̣̌́ṣ̌́ā ḍṣ̣̌́šṣ̌́). Wordfive (Wṣ̣̌́ṣ̌́āḍɪ́ṣč), wordsix (ṭṣ̣̌́ṣ̌́āš̄́ɪ́ū́)
word100 (ṭṣ̣̌́ṣ̌́ā1)
word200 (ṭṣ̣̌́ṣ̌́ā)


It means that i need to put first element of Column A in braces "()" in-front of first element of Column D. The separator position and number in both columns are the same.

Thanks for each help and suggestion in advance.

[Parth007]

I know abt concat of two cells...

Formula (=ConCatRange(A2:D5))

Please Login or Register  to view this content.

but this one is new for me too..

[wali]

Dear Parth, thank you very much. Unfortunately i am not getting the desired result. The Elemens in Column A and D have the same separators at same positions but the length of elements are not always the same.
I need them to be put together like:

wordone (ṭṣ̣̌́ṣ̌́āṣ̣̌́ṣ̣̌́́č), wordtwo (ṭṣ̣̌́ṣ̌́āš́ṭṣ̣̌́); word three (ṭṣ̣̌́ṣ̌́ā š́ə́ṣ̌́čč): word four (ṭṣ̣̌́ṣ̌́ā ḍṣ̣̌́šṣ̌́). Wordfive (Wṣ̣̌́ṣ̌́āḍɪ́ṣč), wordsix (ṭṣ̣̌́ṣ̌́āš̄́ɪ́ū́)

[wali]

Has anyone any other idea how to solve my problem?

[Jacc]

I will give it a try now

[Jacc]

This is hard, give me a couple of days (I only set aside so much time per day for this hobby ). Anyone else feel free...

[Jacc]

It seems to work for the data you supplied. There is an extra space in cell F3 which I believe is a mistake in the data.
Here is the UDF code. The macro code is also supplied in another module.

Please Login or Register  to view this content.

[wali]

Dear Jacc, thank you alot. It seems to be exactly doing, what i need it to.
But it gives me only results for F8. All other cells remain empty. Can you please have a look at the dummy file i am sending.

[Jacc]

The code named StackTrans is basically the same code but it is in a form of a UDF (User Defined Function). You can use this as any other Excel function.
I don't know which you prefer but on the sample sheet you sent me I think it's easier to use the UDF. If you click in the yellow cells in the right column you see the UDF. In the workbook you sent me you put in the same function and copy down.

However, the new sheet you sent me is different, there is no space between the words. Because of this the results are not correct. I will see if I can make it accept space or no space.

[Jacc]

The code will now use space in the results regardless if there are any space in the original text or not.

[wali]

Dear Jacc, it works like a dream. Thaaank you. You are great.

[Jacc]

''''''''''''''''''''''''