Seperating Data From One Column Into 2 Seperate Columns

toddroys

New Member
Joined
Mar 28, 2013
Messages
7
Hi,

I have a glossary with 400+ rows in column A. See examples below. I'd like to separate them so that the term is in column A and the definition is in column B. Once Done I will not need the <SEPARATOR>. Normally instead of that <SEPARATOR> tag I would have a hyphen separate the term from definition but the fact that some terms have a hyphen like "D-VHS" was throwing me further. In the end I will not need a separator because everything will be in separate cells.

DSL <SEPARATOR> Digital Subscriber Line is a technology for bringing high-bandwidth information to homes and small businesses over ordinary copper telephone lines. A DSL line can carry both data and voice signals and the data part of the line is continuously connected.

D-VHS <SEPARATOR> Connects a digital audio/video cable for use with some D-VHS digital VCRs.

E-Mail <SEPARATOR> Messages sent to a customer's receiver that are viewed on their television screen. Pending messages are indicated by an icon on the on-screen status display (channel marker) and by having the Power button flash. (Not all receivers have a blinking light.)

Thanks,

Todd
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
In B1 enter:

=LEFT(A1,FIND(" ",A1)) and copy down

In C1 enter:

=MID(A1,FIND(" ",A1)+1,9999) and copy down

Then copy both columns B & C and PasteSpecialValues back onto those columns.

Then delete column A
 
Upvote 0
Thanks for the quick reply! Sorry, newbie here. It looks like my original message had instances of the word SEPARATOR enclosed in carets removed. See below without the carets. So what you sent works, sort of, but only separates the first word when some terms have multiple words. For example below, the term that needs to be separated is two words, and what you provided separates only the first word "Conditional".

Conditional Access SEPARATOR The process by which customers are authorized to view only specific programming (subscription services or events). The authorization is sent from the CAMC and stored on the access card in the customer's receiver.

<colgroup><col></colgroup><tbody>
</tbody>


Original message with words added back in.
Hi,

I have a glossary with 400+ rows in column A. See examples below. I'd like to separate them so that the term is in column A and the definition is in column B. Once Done I will not need the SEPARATOR. Normally instead of that SEPARATOR tag I would have a hyphen separate the term from definition but the fact that some terms have a hyphen like "D-VHS" was throwing me further. In the end I will not need a separator because everything will be in separate cells.


DSL SEPARATOR Digital Subscriber Line is a technology for bringing high-bandwidth information to homes and small businesses over ordinary copper telephone lines. A DSL line can carry both data and voice signals and the data part of the line is continuously connected.

D-VHS SEPARATOR Connects a digital audio/video cable for use with some D-VHS digital VCRs.

E-Mail SEPARATOR Messages sent to a customer's receiver that are viewed on their television screen. Pending messages are indicated by an icon on the on-screen status display (channel marker) and by having the Power button flash. (Not all receivers have a blinking light.)

Thanks,

Todd

In B1 enter:

=LEFT(A1,FIND(" ",A1)) and copy down

In C1 enter:

=MID(A1,FIND(" ",A1)+1,9999) and copy down

Then copy both columns B & C and PasteSpecialValues back onto those columns.

Then delete column A
 
Upvote 0

Forum statistics

Threads
1,196,288
Messages
6,014,492
Members
441,823
Latest member
GregG26

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top