PDA

View Full Version : Solved: Find Last Comma



MRichmond
05-18-2011, 01:51 AM
Hi there experts,

Looking for a little help with something that I'm sure is very simple, but I just cant seem to get the right answer.

I have a workbook that contains a worksheet (called Main List) with a list of names and addresses in column A, eg. in A2 I have Mr Tony Harper, 19 Alder Root, Werneth, Whitley Bay

In a second workbook I need to enter a formala that find the last comma in the addess in A2 of the first workbook, and give me what is after the last comma eg. Whitley Bay.

I have this formula,
=RIGHT('Main List'!A2,LEN('Main List'!A2)-FIND(",",'Main List'!A2))
but it seems to give me everything after the first comma not the last.

Does anyone know how i can modify my formula to give me the result i'm looking for.

As always, thanks in advance for any help given.

Charlize
05-18-2011, 02:29 AM
You could try

=MID('Main List'!A2;FIND(",";'Main List'!A2;LEN('Main List'!A2)-FIND(",";'Main List'!A2;1))+2;LEN('Main List'!A2))
Be warned, it's a translation from dutch to english.

MID = part of your string you want
FIND = locate all the , and return me the number (last position of ,)
LEN = length of the string

Could be you need to alter the ; to , for separating the parameters.
Charlize

MRichmond
05-18-2011, 02:52 AM
Charlize, thanks very much for the help.

When i made the suggested change it worked like a dream.

thanks again.