Return Characters from Text Cell

Try,

=RIGHT(A1,FIND(" ",A1)-1)

Try this,

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))


Raj

Thank you all very much for kindly contributing and helping...

Alpha and Raj - your advice was perfect and equally to Brian, however, unfortunately that solution didn't work for me.

Alpha: I used your formula with a number of tests and it works perfectly no matter how much I try different scenarios attempting to trip it up, delivers the last part each and every time. So that I can learn, could you help run through the logic of it?

Raj: Likewise with yours too and find it impressive that you both provided the solution with different ways - isn't Excel amazing. I don't get how it works and have never come across the REPT function before? Interested to learn more about that..?

Brian: I tried your approach before posting and couldn't get it to work, appreciate your help nonetheless.

Thank you all again...

I liked MrRajKumar's solution as well. I'll have to remember that one.

=TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",50)),50))

• SUBSTITUTE replaces each space with 50 spaces so that each individual name is separated by 50 spaces.
• Then the RIGHT function clips everything except the last 50 characters. The last 50 characters is just the last name preceded by a bunch of spaces.
• The TRIM function then clips all the preceding spaces from the last name.

=MID(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,999)

• SUBSTITUTE replaces all spaces with nothing and gets the length of the name without any spaces.
• That length is then subtracted from the length of the name with spaces.
• That difference is the count of the number of spaces in the name

=MID(A1,FIND("*",SUBSTITUTE(A1," ","*",count_of_spaces)))+1,999)

• This Substitute replaces only the very last space in the original name with an asterisk
• Then FIND finds the character location of that asterisk in the name
• The MID uses that location as a starting point and returns all the characters after the last space.

a2a said:

Brian: I tried your approach before posting and couldn't get it to work, appreciate your help nonetheless.

Thank you all again...

Click to expand...

Just curious to know what didn't work?

BTW there's a space between the quotes in the formula

AlphaFrog - you are most kind, thank you for explaining logically

Love the way they both work and these solutions will be taken on board to learn from and be applied!

Out of interest, how can you carry out such a task for any sort of spaces? Say - for example - you have FIRSTNAME MIDDLENAME SURNAME or even INITIAL FIRSTNAME MIDDLENAME OTHER NAME SURNAME how would you be able to split each component out?

Brian: Using your formulare with JCVD, only returns "amme". I ammended the '-1' to '+1' - that works, but it doesn't hold solid with other name variations (although for a fair few, it doesn't seem to hold for 'mr a stone' - I got "tone"?).

Thank you again

Split names by using Convert Text to Columns

Ok - so I've managed to get my end result, but as you've greatly contributed by providing so superb recommendations, I wanted to run the reconcillation technique to see if you concur.

<TABLE style="WIDTH: 540pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=719 border=0><COLGROUP><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6070" width=166><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" span=2 width=128><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 103pt; mso-width-source: userset; mso-width-alt: 5010" width=137><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl66 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 125pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 16.5pt; BACKGROUND-COLOR: #d7e4bc" width=166 height=22>A1
Jean Claude Van Damme

</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 96pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #eaf1dd" width=128>B1
Jean

</TD><TD class=xl67 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 96pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #eaf1dd" width=128>C1
Damme

</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 120pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccc0da" width=160>D1
Jean Claude

</TD><TD class=xl68 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 103pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: #ccc0da" width=137>E1
Damme

</TD></TR></TBODY></TABLE>

Green's = Our Data
A1 = Customer Full Name
B1 = Split out the first name using: =LEFT(A1,FIND(" ",A1)-1)
C1 = Split out the surname (last instance of the name) using: =MID(A1,FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))+1,999)

Purple's = Supplier Data (they simply store and have provided data using First Names and Surname)

D1 = First Name
E1 = Surname

I then reconcile D1 with E1 whilst checking that either the first names match entirely, or (as they store sometimes just the initial of the first name) match their first character with the full first name that we have using:

=AND(NOT(ISERROR(MATCH(C1,E1))),OR(NOT(ISERROR(FIND(LEFT(B1,1),D1))),NOT(ISERROR(FIND(B1,D1)))))

Do you have any other idea that I could use to better reconcile based on the above criteria?

Thanks again...