Solved

Find the last occurrence of a character in a string

Posted on 2013-01-14
14
1,352 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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
 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

805 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