Extracting text data from a cell

[Manexcel]

I am trying to extract data from a cell(s) that contains surname, first name, middle name. e.g

bloggs, john charles

I am just after the surname e.g. bloggs

The following formula works well for my data.

=TRIM(MID(SUBSTITUTE(SUBSTITUTE($H12020,",","")," ",REPT(" ",100)),1+100*COLUMNS($H:H)-100,100))

However, I have now noticed some of my data has been entered as;

de smith, LH

The above formula is not working for this type of entered data and I now get an "N/A"

I have tried to manipulate the formula without success. Therefore, can anyone assist in my issue.

I thank you in anticipation.

 

[jtakw]

Hi,

The formula you're using is way overkill if I understand correctly:

Excel 2010
	A	B	C	D
1	bloggs, john charles		bloggs	bloggs
2	de smith, LH		de	de smith
Sheet5

Cell Formulas
Range	Formula
C1	=TRIM(MID(SUBSTITUTE(SUBSTITUTE($A1,",","")," ",REPT(" ",100)),1+100*COLUMNS($H:H)-100,100))
D1	=LEFT(A1,FIND(",",A1)-1)

D1 formula copied down, change Column/Cell reference as needed.

 

[Joe4]

Why not just use:

=LEFT(H12020,FIND(",",H12020)-1)