Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Find the last occurrence of a character in a string

Posted on 2013-01-14
14
Medium Priority
?
1,422 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
  • 2
  • +4
14 Comments
 
LVL 66

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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 49

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 1400 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 31

Assisted Solution

by:hnasr
hnasr earned 600 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 31

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
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…

704 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