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

 

[Weaver]

Here's a solution for B1

=TRIM(SUBSTITUTE(MID(A1,FIND(".",A1)+1,LEN(A1)),MID(A1,FIND("-",A1),LEN(A1)),""))

Still working on C1

 

[Weaver]

I'm thinking a helper column might be the best way - get the portion after the hyphen out first & then test for commas, rather than trying to do it on the fly...I think!

 

[Richard Schollar]

Hi

You can use this in B2:

=TRIM(MID(A1,FIND(".",A1)+1,SUM(FIND({".","-"},A1)*{-1,1})-1))

and this in C2:

=TRIM(MID(A1,FIND("-",A1)+1,2^15))

This won't swap tthe surname/forename around as how would the program know they were swapped?

 

[Weaver]

Richard

Interesting use of the sum in the FIND - never would've thought o' that!

 

[lapta301]

Richard & ExcelR8R

Many thanks for your input. I am at a loss as to what you are doing but its mighty impressive.

Richard fair comment.

What you have both done already is wonderful.

I'll have to have a real close look at what you have done to see if I can try and understand it. Richard what does ^15 do.

Again my sincere thanks to you both

Regards

lapta301

 

[Richard Schollar]

I was just being lazy as regards the MID function - the 2^15 is 2 raised to the power of 15 which is exactly 1 more than the maximum length of a cell (32,767 characters) - ie so the MID manages to extract everything after the hyphen.

 

[lapta301]

Wow

lapta301

 

[lapta301]

Dear all

Following on from my earlier question could I ask if anyone can produce a formula or two that will swap around the Surname and First name that are seperated by a comma ( which should be deleted) and a extra space

i.e. James, Etta ending up as Etta James

Many thanks

 

[Scott Huish]

=trim(mid(a1&" "&a1,find(",",a1&",")+1,len(a1)))