Seperating Data From One Column Into 2 Seperate Columns

[toddroys]

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

 

[Gary's Student]

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

 

[toddroys]

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

 

[Gary's Student]

Then use:

=LEFT(A1,FIND("SEPARATOR",A1)-1)
and
=MID(A1,FIND("SEPARATOR",A1)+10,9999)

 

[toddroys]

Awesome, thank you!