Break up a cell....

[mrpwagner]

I am working on a bibliography. (EndNote) I am converting it to excel. The Author cell looks like this:

Akil, Ali, Ziegeler, Stephan, Reichelt, Jan, Semik, Michael, Müller, Marcus Christian and Fischer, Stefan Several questions: How do I break up the authors and get rid of the and? How can I then covert this to an access database? I have 500 references..... Thanks in advance for your advice!!!

<tbody>
</tbody>

 

[Dr. Demento]

What do you want the output to look like?? Are you open to using VBA or formulas only?

 

[alansidman]

Easily achieved by loading the file to Power Query and applying the following Mcode

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter(", ", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8", "Column1.9", "Column1.10", "Column1.11"}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Split Column by Delimiter", "Column1.10", Splitter.SplitTextByDelimiter(" and ", QuoteStyle.Csv), {"Column1.10.1", "Column1.10.2"})
in
    #"Split Column by Delimiter1"

Once loaded back into Excel, you can then link the table to an Access Data Base

If you are unfamiliar with Power Query then, Power Query-How to use Mcode

 

[sandy666]

or

Column1

Akil, Ali, Ziegeler, Stephan, Reichelt, Jan, Semik, Michael, Müller, Marcus Christian and Fischer, Stefan

Column1.1

Column1.2

Column1.3

Column1.4

Column1.5

Column1.6

Column1.7

Column1.8

Column1.9

Column1.10

Column1.11

Column1.12

Column1.13

Akil

Ali

Ziegeler

Stephan

Reichelt

Jan

Semik

Michael

Müller

Marcus

Christian

Fischer

Stefan

// Table1

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({", " ," and "," "}, QuoteStyle.Csv))
in
    Split[/SIZE]

the rest as above

you didn't show expected result (each name separately or first name and last name together)

 

[mrpwagner]

I need to keep it simple as ..... well i am not as strong on this as I want to be.

 

[mrpwagner]

Thank you!!!! studying this.

 

[mrpwagner]

or

Column1

Akil, Ali, Ziegeler, Stephan, Reichelt, Jan, Semik, Michael, Müller, Marcus Christian and Fischer, Stefan

<tbody>
</tbody>

Column1.1

Column1.2

Column1.3

Column1.4

Column1.5

Column1.6

Column1.7

Column1.8

Column1.9

Column1.10

Column1.11

Column1.12

Column1.13

Akil

Ali

Ziegeler

Stephan

Reichelt

Jan

Semik

Michael

Müller

Marcus

Christian

Fischer

Stefan

<tbody>
</tbody>

// Table1

[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    Split = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByAnyDelimiter({", " ," and "," "}, QuoteStyle.Csv))
in
    Split[/SIZE]

the rest as above

you didn't show expected result (each name separately or first name and last name together)

Thanks!!

 

[Michael M]

Simply highlight the cells Then on the Data tab, select Text to Columns >>Delimited>>check the space checkbox and uncheck any others>>Finish

Delete the "and" column !!

 

[sandy666]

Thanks!!

You are welcome
Have a nice day