Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I have a whole list of entries in two columns say ColA as

dn: uid=HM_SERVICE1,cn=users,dc=somedomain,dc=main,dc=org
objectclass: inetorgperson
objectclass: top
objectclass: person
objectclass: organizationalPerson
sn: HM - SERVICE1
cn: HM_SERVICE1
userpassword: xxxx
uid: HM_SERVICE1

and Columns B has a value of say HM_JOSHUASERVICE

What I am trying to achieve is replace in column A, wherever HM_SERVICE1 exists with the new value in Column B so my end result woudl be

dn: uid=HM_JOSHUASERVICE,cn=users,dc=somedomain,dc=main,dc=org
objectclass: inetorgperson
objectclass: top
objectclass: person
objectclass: organizationalPerson
sn: HM - JOSHUASERVICE
cn: HM_JOSHUASERVICE
userpassword: xxxx
uid: HM_JOSHUASERVICE

I heard that this can be easily done in excel , can someone please hel pme with this?

share|improve this question

migrated from stackoverflow.com Oct 11 '12 at 16:33

This question came from our site for professional and enthusiast programmers.

    
In A, is the multi-line text from dn: to uid in a single cell or a line per cell? –  Alex K Jun 30 '11 at 10:14
    
the multi-line text is in a single cell –  Kheat Jun 30 '11 at 10:27
    
Can't you just =SUBSTITUTE(A1,"HM_SERVICE1",B1) in C1? –  Alex K Jun 30 '11 at 10:28
    
LOL, yeah that does the job too, will check more and let you know. One Question, When I check the output it is now in quotes, can I remove them? –  Kheat Jun 30 '11 at 10:34
    
Whats in quotes? it should not add anything else –  Alex K Jun 30 '11 at 10:36

2 Answers 2

up vote 1 down vote accepted

In C1 you should be able to use;

=SUBSTITUTE(A1,MID(A1,SEARCH("uid=",A1,1)+4,SEARCH(",",A1,1)-SEARCH("uid=",A1,1)-4),B1)
share|improve this answer

If you know RegEx I found a good article that quickly tells you how to configure you're environment so that you can use them in Excel. It's here. Hope that helps.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.