Find last occurence of a character in excel

I can’t even remember why now but fairly recently I have needed to take a string/text in excel and find the last occurence of a particular character so that I could manipulate the data. Despite searching the internet all I could find was complex functions that didn’t quite do the job and unforutnately Excel doesn’t come with any inbuilt functions like for example ’strrchr’ in PHP.

Surely you would think this was easy to do and documented somewhere but I couldn’t find anything so set out to use some simple inbuilt Excel functions without writing Excel modules. The following is a working example and some theoritical code.

Example

Say for example I have the following in cell A1 - c:\directory\temp1\temp2\temp3 and want to be able to remove the last directory. To be able to do this I first need to find the last backslash \ and then remove the text from there. Sounds easy but the is no function for finding the last character, if there was only one backslash then no problem but there are multiple.

Step 1 (data in cell A1 forumla in B1)

First you need to be able to count the the number of occurences of the character, the following forumla will do just that :

=IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,”\”,”")))

which hopeful returns 4

Step 2 (data in cell B1 forumla in C1)

Thankfully Excel does come with an inbuilt substitution function which will allow you to change a character for a certain occurance. So next you need to replace the final backslash with something unique which in this case is the asterix.

=SUBSTITUTE(A1,”\”,”*”,B1)

this should then return c:\directory\temp1\temp2*temp3

Step3 (data in cell C1 forumla in D1)

Now you have a unqiue charater you should be easily able to find the postition of this within the string using :

=FIND(”*”,C1)

which should return 25

Step 4 (data in cell E1 forumla in D1)

You can then easily display all the text to the left of the last backslash or to the right of the backslash using the following :

=MID(A1,1,D1) returns c:\directory\temp1\temp2\

=RIGHT(A1,LEN(A1)-D1) returns temp3

Step 5 (data in cell A1 forumla in B1)

The complete forumla in cell B1 for both scenarios would be :

=MID(A1,1,(FIND(”*”,(SUBSTITUTE(A1,”\”,”*”,(IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,”\”,”")))))))))

returns c:\directory\temp1\temp2\

=RIGHT(A1,LEN(A1)-(FIND(”*”,(SUBSTITUTE(A1,”\”,”*”,(IF(LEN(TRIM(A1))=0,0,LEN(TRIM(A1))-LEN(SUBSTITUTE(A1,”\”,”")))))))))

returns temp3

Obviously if you are changing multiple fields and the strings all have the same number of characters you are trying to match ie 4 backslashes then in theory you do not need to do step 1 because you should already no the number of occurences is 4. The above exmample is for scenarios where you may not always have the same formatted data.

If there are easier ways then let me know

Blog going slow

Sorry, not much been happening here. Unfortunately I’ve been too busy working on my golf community website golfshake.com.

Not that there is a topic to my blog other than my life, techy stuff and golf but I hope to start posting some more techy related stuff. Which will be things that I’ve been working on that were a challenge and I struggled for help either in books or googling which maybe will help others.

Let’s see if I do

Build or buy

Hmmmmmmmmmm. Tough question really, I’m just at stage to work on a few new developements for golfshake.com the online golf community I run and do I write the software myself or use free/paid for off the shelf applications ?

Now I could quite easily write everything myself but should I not just concentrate on my niche areas which is the golf handicap and golf score tracking elements of the site. If I wrote everything then some parts of the site might not see updates for many months, not be as good as similar dedicated applications and not really work.

On the other hand if I purchase off the shelf applications I will get supported and maintained applications that should see regular releases but they will not quite function like I would hope and I would still need to spend time integrating.

I suppose this all comes down to time, money, resource (skills, ability, techs etc) and long term strategy. If I’m talking about a simple online photo gallery for the golfers, golf groups and golf events then it might be worth spending the time developing, as long as I’m not setting out to develop a full blown flickr application.

Follow up articles :

http://web.mit.edu/wwwdev/cgiemail/buybuild.html
http://www.awprofessional.com/articles/article.asp?p=21775&rl=1
http://www.learningcircuits.org/2002/jan2002/elearn.html

Cheerio

5 good SEO links

Seems I’ve only posted recently on my SEO exploits about my golf site (golfshake.com), thought I would post some good links.

Fantastic information on SEO and search engines

Content optimistation

Link baiting vs Directory debate

Keyword optimistation also check out the links on this page

SEO tools

5 links to Keywood tools

Following on from my SEO topics, here are some good links for keywords tools which can be used for content optimisation, meta building, PPC optimistation and anything else you can think of

Keyword selector tool 1

Keyword selector tool 2

Keyword selector tool 3

Keyword selector tool 4

Keyword selector tool 5

SEO - slow or quick

It’s now 2 weeks since I started the search engine optimisation of golfshake.com. To be honest I’ve not spent a great deal of time on this, mainly submitting the site to the major search engines, a few directories and a bit of work on some link building.

It’s good to see that there are now over 14,000 pages being indexed by google for golfshake and a few links coming back in.

Over the next 2 weeks the priority is to continue some link building with relevant partner sites in the golf industry and then optimistation of the golfshake site in terms of key words and content focusing on golf handicap, golf society and golf events.

Cheerio for now and look out for an update later.