How to get only part of the data from complete column value?

[#1]

Hi,

I have a table with columns ID, Fullname, Date. How can I get the table only to show values which are under second name

for eg: I have below sample data I need to show table only based on second name

ID    Fullname                           Date

1     Adam Jonesi                     10/03/2012

2     Jony Flait Taypourt           09/10/2000

3     Kaity Gart Marley              21/09/2013

output table should be like below

ID    name              Date

1     Jonesi            10/03/2012

2     Taypourt          09/10/2000

3     Marley            21/09/2013

Please can anyone suggest me in this.

Thanks.

[#2]

Thanks. It is working when data is in below format

Jiou Jane Kiouy (Nee Tunmore)

but it is not working when data is in below format

Koli McQuillan

how can I get both at a time. Please help

[rubenmarin]

What is expected with this name? McQuillan?

It works for me with the last expression, i attach an example.

[#4]

Thanks. strange for me it is not working. any help please

[rubenmarin]

What is returned with Koli McQuillan? can you upload a sample with the names?

[#6]

Please see attached.

[rubenmarin]

McQuillan had a blank space at the end, try:

=Mid(Trim(Fullname), index(Trim(Fullname), ' ', substringcount(Left(Fullname, Index(Fullname, '(')), ' ')-1))

or

=Mid(Fullname, index(Trim(Fullname), ' ', substringcount(Left(Fullname, Index(Fullname, '(')), ' ')-1))

First one will return McQuillan (without the blank space at the end) the second one also returns the space at the end

[#8]

Thanks. how can I remove the space in Secondname at starting

[perumal_41]

Hi

PFA ,

hope it help to you.

Regards,

Perumal

[rubenmarin · Accepted solution]

Adding a +1 to the mid function should work:

=Mid(Trim(Fullname), index(Trim(Fullname), ' ', substringcount(Left(Fullname, Index(Fullname, '(')), ' ')-1)+1)

[#11]

Thanks. Super. Thanks for your prompt replies.