• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Seperate One Cell To Multiple Cells

Hany ali

Active Member
hello My Masters , I Want Your excellent ability To Help me to make text Seperate from one Cell to Multiple Cells as Example ...Start From Cell A4 For All Column A,But Some Time this Text From Cell A4 With Separater "-" and Some Times Without Separater as You See In EXCEL Sheet ,,,,,, with my Best Regards ... Please I Want Function work with English Or Arabic Text
 

Attachments

  • Seperate.xlsx
    12.3 KB · Views: 7
  • Untitled.png
    Untitled.png
    42.9 KB · Views: 5
Last edited:
Try,

1] Split text with separator "-".

In B4, formula copied across right :

=TRIM(MID(SUBSTITUTE("-"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A4,"Class","Class-"),"helper","helper-"),"Great","Great-"),"-",REPT(" ",199)),COLUMN(A$1)*199,199))

2] Split text without separator.

in B5, formula copied across right :

=TRIM(MID(SUBSTITUTE("-"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A5,"Class","Class-"),"helper","helper-"),"Great","Great-"),"/1 ","/1-"),"-",REPT(" ",199)),COLUMN(A$1)*199,199))

79496
 
Excellent, you are a genius, you work efficiently.. but I wish there was a solution in the text in Arabic, so do not work with it and see for yourself
Code:
=TRIM(MID(SUBSTITUTE("-"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A4,"درجة","درجة-" ),"مساعد","مساعد-"),"كبير","كبير-"),"-",REPT(" ",199)),COLUMN(A$1)*199,199))
 

Attachments

  • Seperate.xlsx
    13.3 KB · Views: 2
  • Untitled.png
    Untitled.png
    42.2 KB · Views: 4
Last edited:
Excellent, you are a genius, you work efficiently.. but I wish there was a solution in the text in Arabic, so do not work with it and see for yourself
Code:
=TRIM(MID(SUBSTITUTE("-"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($A4,"درجة","درجة-" ),"مساعد","مساعد-"),"كبير","كبير-"),"-",REPT(" ",199)),COLUMN(A$1)*199,199))
@Hany ali

Sorry, my English version Excel unable to read Arabic text.

So, I cannot give you any help.

Regards
 
Last edited:
There is no solution when the text is in Arabic?
@Hany ali,

Just advise you, try go to the Arabic language Excel discussion group and solve problem by theirs.

Remark : when my English Excel open your file, I find that all words are reversed from left to right like a mirror image.

That means I can't help you without understanding the contents.

Regards

bosco_yip
 
Last edited:
Back
Top