Break up a cell....

[mrpwagner]

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

[TABLE="width: 817"]
<tbody>[TR]
[TD="width: 817"]Akil, Ali, Ziegeler, Stephan, Reichelt, Jan, Semik, Michael, Müller, Marcus Christian and Fischer, Stefan

Several questions:

1. How do I break up the authors and get rid of the and?
2. How can I then covert this to an access database?

I have 500 references.....

Thanks in advance for your advice!!!


[/TD]
[/TR]
</tbody>[/TABLE]

 

[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
[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Akil, Ali, Ziegeler, Stephan, Reichelt, Jan, Semik, Michael, Müller, Marcus Christian and Fischer, Stefan[/td][/tr]
[/table]

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1.1[/td][td=bgcolor:#70AD47]Column1.2[/td][td=bgcolor:#70AD47]Column1.3[/td][td=bgcolor:#70AD47]Column1.4[/td][td=bgcolor:#70AD47]Column1.5[/td][td=bgcolor:#70AD47]Column1.6[/td][td=bgcolor:#70AD47]Column1.7[/td][td=bgcolor:#70AD47]Column1.8[/td][td=bgcolor:#70AD47]Column1.9[/td][td=bgcolor:#70AD47]Column1.10[/td][td=bgcolor:#70AD47]Column1.11[/td][td=bgcolor:#70AD47]Column1.12[/td][td=bgcolor:#70AD47]Column1.13[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]Akil[/td][td=bgcolor:#E2EFDA]Ali[/td][td=bgcolor:#E2EFDA]Ziegeler[/td][td=bgcolor:#E2EFDA]Stephan[/td][td=bgcolor:#E2EFDA]Reichelt[/td][td=bgcolor:#E2EFDA]Jan[/td][td=bgcolor:#E2EFDA]Semik[/td][td=bgcolor:#E2EFDA]Michael[/td][td=bgcolor:#E2EFDA]Müller[/td][td=bgcolor:#E2EFDA]Marcus[/td][td=bgcolor:#E2EFDA]Christian[/td][td=bgcolor:#E2EFDA]Fischer[/td][td=bgcolor:#E2EFDA]Stefan[/td][/tr]
[/table]

// 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
[TABLE="class: head"]
<tbody>[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #5B9BD5"]Column1[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #DDEBF7"]Akil, Ali, Ziegeler, Stephan, Reichelt, Jan, Semik, Michael, Müller, Marcus Christian and Fischer, Stefan[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: head"]
<tbody>[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #70AD47"]Column1.1[/TD]
[TD="bgcolor: #70AD47"]Column1.2[/TD]
[TD="bgcolor: #70AD47"]Column1.3[/TD]
[TD="bgcolor: #70AD47"]Column1.4[/TD]
[TD="bgcolor: #70AD47"]Column1.5[/TD]
[TD="bgcolor: #70AD47"]Column1.6[/TD]
[TD="bgcolor: #70AD47"]Column1.7[/TD]
[TD="bgcolor: #70AD47"]Column1.8[/TD]
[TD="bgcolor: #70AD47"]Column1.9[/TD]
[TD="bgcolor: #70AD47"]Column1.10[/TD]
[TD="bgcolor: #70AD47"]Column1.11[/TD]
[TD="bgcolor: #70AD47"]Column1.12[/TD]
[TD="bgcolor: #70AD47"]Column1.13[/TD]
[/TR]
[TR="bgcolor: #FFFFFF"]
[TD="bgcolor: #E2EFDA"]Akil[/TD]
[TD="bgcolor: #E2EFDA"]Ali[/TD]
[TD="bgcolor: #E2EFDA"]Ziegeler[/TD]
[TD="bgcolor: #E2EFDA"]Stephan[/TD]
[TD="bgcolor: #E2EFDA"]Reichelt[/TD]
[TD="bgcolor: #E2EFDA"]Jan[/TD]
[TD="bgcolor: #E2EFDA"]Semik[/TD]
[TD="bgcolor: #E2EFDA"]Michael[/TD]
[TD="bgcolor: #E2EFDA"]Müller[/TD]
[TD="bgcolor: #E2EFDA"]Marcus[/TD]
[TD="bgcolor: #E2EFDA"]Christian[/TD]
[TD="bgcolor: #E2EFDA"]Fischer[/TD]
[TD="bgcolor: #E2EFDA"]Stefan[/TD]
[/TR]
</tbody>[/TABLE]


// 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