Solved

Find the last occurrence of a character in a string

Posted on 2013-01-14
14
1,382 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 47

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
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…

752 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