String Extraction Question

[lapta301]

Dear All

Could I trouble you to achieve the following.

In cell A1 I have the text
20. Black Angel Blues (Sweet Black Angel) - Nighthawk, Robert

In cell B2 I would like all of the text from the period/full stop after '20' until the - hyphen character

In cell C2 I would like the text after the hypen, but if as in this case there is a surname followed by a first name I would like them rearranged so that the full name is shown first name followed surname without a comma.

The end result in this example would be:

B2
Black Angel Blues (Sweet Black Angel)

C2
Robert Nighthawk

I hope that this makes sense.

As always many thanks to you all


lapta301

 

[lapta301]

HOTPEPPER

Great works a treat - thank you very much.

I'll have to try and understand how this works.

Regards


Lapta301

 

[Scott Huish]

Here's it broken out if it helps:

Excel Workbook
	A	B	C	D	E	F
1	Smith, John	=A1&" "&A1	=FIND(",",A1&",")+1	=LEN(A1)	=mid(A1&" "&A1,C2,D2)	=trim(E2)
2		Smith, John Smith, John	7	11	John Smith	John Smith
Sheet1

 

[lapta301]

HOTPEPPER

That's great thank you so much for taking the time to explain it.

Again my sincere thanks from a wet and gloomy England.