Extract name from cell formula

[orsm6]

Hi all, i have a massive list Column B containing a string of text. How can I extract only the name that is in the cell?

if it helps, every single row in column B begins with last name, first name then the word 'was'.
it basically looks like this: Citizen, John was granted access

thinking about it, i would like to go one step further. how can i get the last name in column C and first name in column D?
example

Occurrence Time	Event Full Description	Last	First
1/06/2020 12:00:21 AM	Citizen, John was granted access	Citizen	John

it also looks like the raw data has two lines (in the formula bar) of text. like as if you press Alt+enter to get a second line

TIA

 

[orsm6]

I think that i might have to actually separate more of the data. i need the door number and description

this is the full string: Citizen, John was granted entry into 41 - Factory Entry through 41 - Factory Entry.

Occurrence Time	Event Full Description	Last	First	Door	Description
1/06/2020 12:00:21 AM	Citizen, John was granted entry into 41 - Factory Entry through 41 - Factory Entry. Card number (14375)	Citizen	John	41	Factory Entry through 41

 

[jmacleary]

Hi there. There may be more elegant ways, but this should do it:

Book1
	A	B	C	D
1	Occurrence Time	Event Full Description	Last	First
2	01/06/2020 00:00	Citizen, John was granted access	Citizen	John
3	02/06/2020 00:00	Citizen, John was granted access	Citizen	John
4	03/06/2020 00:00	Arthurian, Albert was a king	Arthurian	Albert
5	04/06/2020 00:00	Washington, George was a president	Washington	George
Sheet1

Cell Formulas
Range	Formula
C3:C5	C3	=LEFT(B3,FIND(",",B3)-1)
D3:D5	D3	=MID(B3,FIND(",",B3)+2,FIND("was",B3,FIND(",",B3)+2)-FIND(",",B3)-3)

 

[Peter_SSs]

One example is not much to go on.
How do we logically determine the door number and description?

For the first couple, try ..

20 08 04.xlsm
	B	C	D
1	Event Full Description	Last	First
2	Citizen, John was granted entry into 41 - Factory Entry through 41 - Factory Entry. Card number (14375)	Citizen	John
Sheet2 (2)

Cell Formulas
Range	Formula
C2	C2	=LEFT(B2,FIND(",",B2)-1)
D2	D2	=REPLACE(LEFT(B2,FIND("was",B2)-2),1,LEN(C2)+2,"")

 

[orsm6]

Hi both.... thanks for your help. yeah sorry, I didn't want to copy people's names into here so only gave one example.

It is a bit messy, but i stumbled on converting text to columns. it essentially did the same thing, but not as neat haha.

thank you for your help.

 

[orsm6]

I was able to manage to get the data with your formulas

 

[jmacleary]

Well done.