Excel Formula to separate Word in a Text

hi, i have a text as follows:

Gedung Grha Xl, Jl. Dr. Ide Anak Agung Gde Agung Lot E4-7 No.1 Kuningan Timur-Setiabudi Jakarta Selatan, Dki Jakarta Raya 12950

i want the text to separate into different columns lets say column B and C.

i have the following formula to do the job

=TRIM(LEFT(LEFT(A1,59),FIND("^^",SUBSTITUTE(LEFT(A1,59)," ","^^",LEN(LEFT(A1,59))-LEN(SUBSTITUTE(LEFT(A1,59)," ",""))))))

&

=SUBSTITUTE(A1,B1,"")

which gives the above text in

column B = "Gedung Grha Xl, Jl. Dr. Ide Anak Agung Gde Agung Lot E4-7"
column C = " No.1 Kuningan Timur-Setiabudi Jakarta Selatan, Dki Jakarta Raya 12950"

Now i wanted a formula that will divide the text into 3columns which the second column will cut the character at 28 for example:

Column B = "Gedung Grha Xl, Jl. Dr. Ide Anak Agung Gde Agung Lot E4-7"
Column C = " No.1 Kuningan"
Column D = "Timur-Setiabudi Jakarta Selatan, Dki Jakarta Raya 12950"

Please ensure that the results will give the full wording and doesn't cut the word into half. Also, there will be no Column D if the text is not long enough.

Thanks

Hello su2009,

These formulas are not very useful if you have different address information!

They will only work if the number of characters in the first line is constant.

For example, if Dr Ide Anak was Dr David Anak, it would fail.

If you want to break these addresses up, you need to have some other form id delimiter inserted in the address line.

I hope this helps, please let me know!

Regards,

David


- Please click on the *Add Reputation button at the bottom of helpful responses.

Please mark your thread as SOLVED:
- Click Thread Tools above your first post, select "Mark your thread as Solved".

hi David,

Do you have any other formula that can help me? As i do not have any other form id delimiter