Solved

Find the last occurrence of a character in a string

Posted on 2013-01-14
14
1,346 Views
Last Modified: 2013-01-25
I have a list of names. I want to separate out the last name from this list. The list contains names in the following configurations.
1. A Billards
2. A A Billards
3. A & B Billards

I need to find the last occurrence of a space character in the text string of the name.

I have tried various solutions. I added a User Defined Module that reversed the character string so I could then find the first occurrence and then re reverse it. Problem was that when I saved my UDM 2 things happened. 1. The excel file reverted to an earlier version and I lost all the work I was doing. 2. when I went to use the UDM again, I got a #NAME error that I was not able to correct. Probably there was some security setting on my computer that prevents UDM's from working and I don't know how to correct that setting.

Is there a simpler way to do this?

I also tried the Find and Substitute functions but I have not been able to get them to work properly.

I would prefer to use existing excel functions and avoid VBA script.
0
Comment
Question by:Scruples89
  • 5
  • 2
  • 2
  • +4
14 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38774907
I'm guessing there's a more elegant way to pull this off, but the below works in Acces VBA:

Public Function fn_last_position(sPassedString as String, sSearchString as string) as Integer

fn_last_position = Len(sPassedString) - InStr(1, StrReverse(sPassedString), sSearchString) + 1

End Function

-- To test
?fn_last_position("A & B Billards", " ")
   6
0
 
LVL 9

Expert Comment

by:TazDevil1674
ID: 38774911
If those values are in Column A, type the following in B & C

b - =LEFT(A3,MAX((MID(A3,ROW($1:$70),1)=" ")*ROW($1:$70)))
c - =RIGHT(A3, LEN(A3) - LEN(B3))

For the Formula in B - its and Array Forumula, when you type it you need to press Ctrl+Shift+Enter to get it to work, not just Enter

Example
0
 

Author Comment

by:Scruples89
ID: 38774942
What happens if I have 10,000 name records I need to process? all of them are in rows.
0
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38774943
In VBA you can use intPos = InStrRev(<string>, <string to match>)

It looks left to right.
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38774946
Try these non-array formulas
=REPLACE(A1,FIND("#",SUBSTITUTE(A1," ","#",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),9999,"")
=REPLACE(A1,1,LEN(B1)+1,"")
0
 
LVL 9

Expert Comment

by:TazDevil1674
ID: 38774964
If you input the formula as I suggested, you can drag/fill down/copy+paste the formula down and it works correctly

As long as the one you copy from is entered correctly
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38775007
Assuming data in A2 you can get everything after the last space with this formula in B2:

=TRIM(RIGHT(SUBSTITUTE(TRIM(A2)," ",REPT(" ",99)),99))

Put cursor on the bottom right of B2 until you see a black "+" - that's the "fill handle" - double-click and the formula will fill down as far as you have continuous data in the adjacent column

regards, barry
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:Scruples89
ID: 38775116
Hi Barry, Thanks! How does this formula work? What is the REPT function and how does it generate the result?
0
 

Author Comment

by:Scruples89
ID: 38775122
Also, if I want to get the text preceding the last name into a Given Names Field. How do I do that?
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 350 total points
ID: 38775589
REPT(" ",99) just generates a string of 99 spaces - SUBSTITUTE function then replaces every space in your original data with that string of 99 spaces....and RIGHT function then takes the last 99 characters from the result.....so as long as the last word in your result doesn't contain more than 99 characters the result of that will be many spaces followed by the last word - the initial TRIM function "trims" off those spaces leaving you with the last word only.

Lets assume you put that formula in C2 then for the rest of the data use this formula in B2

=TRIM(LEFT(TRIM(A2),LEN(TRIM(A2))-LEN(C2)))

Both formulas will also cope with any additional spaces you may have in the data

regards, barry
0
 
LVL 30

Assisted Solution

by:hnasr
hnasr earned 150 total points
ID: 38775666
I go with barryhoudini!

Explanation of formula for 9 characters:
B1=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",9)),9))

Representing " " as ^ for clarity
TRIM             Right                              Substitue                                       TRIM                  String
 Billards <== ^Billards<==   A^^^^^^^^^&^^^^^^^^^B^^^^^^^^^Billards <== A & B Billards <== A & B Billards

Begging of string:

C1=TRIM(SUBSTITUTE(A1,B1,""))

To copy formulas:
Split sheet vertical/horizontal
Show start cell in one pane, and end cell in other pane
Copy formula
Select range
Paste
0
 

Author Comment

by:Scruples89
ID: 38775704
Okay that is looking good, I will give it a try and see how it works.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 38775746
I see that barryhoudini posted an explanation while I was figuring a simple pictorial one.
0
 

Author Closing Comment

by:Scruples89
ID: 38821361
Thanks for your help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now