Return Characters Surrounding Specific Character

[Tap13]

I have a series of data that resides in my A column and somewhere in each portion is a persons name. The data typically appears like this:

1083Ak2 Smith, Joe 34kl 1000

What I would like to do is have a formula in B1 that returns "Smith, Joe". Right now my code is:

=Trim(MID(A1,Find(",",A1) -12,25))

It works to return the name but because it uses a literal number of characters to work with it periodically returns something like:

AK2 Smith, Joe 34k

Can I somehow return the characters surrounding the comma, but only until it hits a space?

Thanks for everyones help on this.

 

[AlphaFrog]

This will return the text between the 1st and 3rd space characters in the original text.

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ", 100)),100,200))

 

[djreiswig]

Does the name always start after the first space character?

If not, then you would need to find a way to find the first space before the comma and then the second space after the comma. I don't know of a way to do this with a formula. I haven't found a way to use find before the comma. I'm sure it could be done with VBA if this is an option for you.

 

[Tap13]

There are many spaces throughout the data. The only thing that is constant is that the layout for names is always "Last, First", beyond that there is a lot of fluctuation.

 

[AlphaFrog]

Is there always only one comma?

 

[AlphaFrog]

This will return the text (names) surrounding the 1st comma.

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",100)),FIND(",",SUBSTITUTE(A1," ",REPT(" ",100)))-100,300))

 

[djreiswig]

Very creative. I didn't know that trim also removed internal spaces.

This could fail if the name is really, really, really long, but that should never happen.

 

[Tap13]

PERFECT! You sir are fantastic. That worked perfectly. Appreciate your quick response too, thank you.