How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Richard Your Own Question
Richard, Software Specialist
Category: Microsoft Office
Satisfied Customers: 36526
Experience:  Over 15 year experience resolving Microsoft Office Issues
Type Your Microsoft Office Question Here...
Richard is online now
A new question is answered every 9 seconds

If I have a field which contains enterprise.directory_service.active_directory.n/a I

This answer was rated:

If I have a field which contains enterprise.directory_service.active_directory.n/a

I want to have 4 columns created from this filed i.e.

Col B enterprise
Col C directory_service
Col D active_directory
Col E n/a
Thank you for your question, my name is Richard.

You would need to split it by using these for statments.

In my example I assume A1 has the main statement

Col B: =left(A1,10)
Col C: =mid(A1,12,17)
Col D: =mid(A1,30,16)
Col E: =right(A1,3)

If you have any problems, or would like any additional information or assistance, please do not hesitate to let me know.

And if you can take a second to rate my service, that would be greatly appreciated.

Thank you
Customer: replied 3 years ago.



I know those strings the challenge I have is that the amounts are not all the same i.e. below some of the fields i want to split:












ok, but if you want to do it for any string, there needs to be a common element.

eg: always 4 dots in it which depict a word.

Are any of these elements existing?
Customer: replied 3 years ago.

yes there is 4 dots in every string.

ok, then you want to use this,

Col B: =LEFT(A1,FIND(".",A1)-1)
Col C: =SUBSTITUTE(MID(A1,FIND(".",A1),FIND(".",A1,FIND(".",A1)+1)-LEN(C8)),".","")

Col D: =SUBSTITUTE(MID(A1,FIND(".",A1,FIND(".",A1)+1),FIND("a",A1,FIND("a",A1)+1)),".","")

Col E: =RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND(".",A1,FIND(".",A1))+1)+1))

I have also done an example for you which you can download here

If you have any difficulties at all, please do not hesitate to let me know so I can assist you further

Thank you
Customer: replied 3 years ago.

Hi i use the example and get:


It works for me on your examples.

Which string did you use as I cannot make out your reply
I uploaded another example, using all the values you gave me

Let me know if you have any difficulties please

Customer: replied 3 years ago.



Col D for all bar the 1st one is cut off

I saw the bug with this and corrected it.

Please check this file

Let me know if you have any difficulties please

Richard and 3 other Microsoft Office Specialists are ready to help you
Customer: replied 3 years ago.


Thanks so very much



Related Microsoft Office Questions