Change the data in ; as a separate row in excel spreadsheet

krish5music
krish5music used Ask the Experts™
on
Team,

I have spreadsheet that contains lots of entries in one row which is updated as ;

For example.
Name     Access
xyx          domain\ambc;domain\bbbcb

I need to convert it to the below format. I mean i want it in a separate row.

Name  Access
xyx        domain\ambc
              domain\bbcb
Please let me know if it possible in excel.

THanks
krish
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
Yup. Look at text to columns.

2019-03-11-09_22_17-Book1---Excel.png
2019-03-11-09_22_48-Book1---Excel.png
HTH,
Dan

Author

Commented:
Hello Dan,

I want the entries in a separate rows instead of columns.

I have already tried that option, but for each entries i need to manually copy from column and create new row and paste it.

Its very time consuming. Please let me know if there is any other option available.

THanks
Kaushik

Commented:
If you only had domain\ambc;domain\bbbcb;domain\xxx etc, it would be trivial. Use text to columns and then paste special->transpose.

You can save the file as csv (or copy/paste) and use Notepad++ or any other text editor that supports regular expressions.
Then find/replace as below.

that's a space before the +Note: that's a space before the +

2019-03-11-09_51_11-_new-22---Notepa.png
After that simply copy/paste the text in excel and use text to columns, using tab as delimiter. The result:
2019-03-11-09_52_13-Book1---Excel.png
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Or you could use VBA. Click the blue button in the attached workbook.
29138730.xlsm
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial