473,324 Members | 2,268 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Extract last names with variable formats?

Hi... I need some help with either a formula or vba routine that will extract last names for a comparison. The names can come into my spreadsheet as any of the following
John Doe
John J Doe
Jonh Jason Doe
John Jason Lee Doe
I've already stripped out all of the suffixes (Jr., Sr, CFA, MD, etc) and am using the following formula. The problem is, for some reason some of the results are blank. I copied this formula from another sheet, but am having trouble following exactly what it's doing. Any help is GREATLY appreciated!

Expand|Select|Wrap|Line Numbers
  1. = RIGHT(J2,LEN(J2)-FIND("*",SUBSTITUTE(J2," ","*",LEN(J2)-LEN(SUBSTITUTE(J2," ",""))))))
Cell J2 contains the full user name, which is to be compared to another name value from another system which will contain a similar formula.

THANK YOU!
Nov 14 '08 #1
3 1404
debasisdas
8,127 Expert 4TB
findout the position of the space from the right side .
From that position+1 read to the end for the last name.
Nov 15 '08 #2
Hi there.

You can use the property lastindexof to find the last space and get the last word

The example below show you how to get the last word typed in TextBox1 and put in Label1.Text

I hope this help you.


Expand|Select|Wrap|Line Numbers
  1.  
  2.     Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
  3.         Me.Label1.Text = Me.TextBox1.Text.Substring(Me.TextBox1.Text.LastIndexOf(" ") + 1)
  4.     End Sub
  5.  
  6.  
Nov 16 '08 #3
Thanks a million for the help!
Nov 18 '08 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Fernando Rodriguez | last post by:
Hi, How can I extract all the strings in an exe with python? O:-)
3
by: Phil | last post by:
Hei, I'm currently trying to write a program in C# that will allow users to parametrize their queries. For instance, I have a query like this: SELECT * FROM Customers Where Region = @Region AND...
9
by: Stefan Franke | last post by:
Hi, I've got the following simple XSLT stylesheet, that lists all the values of the elements of any given XML file. <?xml version="1.0" encoding="ISO-8859-1"?> <xsl:stylesheet version="1.0"...
4
by: rdraider | last post by:
Is there a function that will extract part of a string when the data you want does not occur in a specific position? Field "REF" is varchar(80) and contains an email subject line and the email...
2
by: Alan | last post by:
I'm having a bit of difficulty getting the results I need from our database. In a nutshell I'm trying to work out trends in what people buy next. So, for example, I'm trying to run a query that...
7
by: John | last post by:
We have created a game and when the user click on "Download New Songs" which requires the user to download songs. This could be over 20 songs. I have these files zipped on the server in one...
0
by: mix01 | last post by:
Hi, I am trying to get some VBA code working, but am preplex as to why it does not work. I would really appreciate any level of help. Many thanks, Mix01 Version of the program
0
by: lildiapaz | last post by:
How, do i extract first and last names from the census bureau website and add it into an sql 2000 table in c# windows application Here's what i have attempted so far: //class1.c using...
7
by: JoeC | last post by:
I am trying to create a windows program that reads binary graphics as a resource. This has nothing to do with win32 but conversion of data with memcpy. graphic::graphic(UINT uiResID, HINSTANCE...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.