* Extracting text from a string

[LindaO]

Hello,

Be grateful for any help please? I am trying to extract the last place name from an address field using a query.

Each part of the address is separated by a comma but the number of "parts" to the address can vary as can the number of characters in each "part". For example, "24 High Street, The Village, The Town, Any County" (4 parts, 3 commas, 10 characters at end of the string) or "24 High Street, The Town, Any Other County" (3 parts, 2 commas, 16 characters at the end of the string). So I am trying to get the the query to display "Any County" and "Any Other County" from the two examples.

I have used combinations of text functions including TextIf, Section, TextPart and many others but have been unable to determine where in the string the final comma is located so that I can use a combination of TextLen and TextRight to extract the final part - unless there is a better function which I am missing.

Thanks in advance for any assistance,

Linda

[ColeValleyGirl]

Have you tried using TextPart -1 to get the text you want?

https://www.family-historian.co.uk/help ... tpart.html

[LindaO]

I have tried TextPart but I can't get it extract at the last position as number of last position varies - if value was in the first position in the string it would be a problem. Sometimes its the obvious that the solution but so far I am missing how to resolve. I have tried various text functions plus logic functions (e.g. IsTrue, Exists etc) but no luck so far.

Regards,

Linda.

[tatewise]

The help page for the =TextPart function gives an example of that:

Assuming the input text (%INDI.BIRT.PLAC%) contains "one, two, three, four, five, six, seven, eight", the following function calls will return the values shown:

=TextPart( %INDI.BIRT.PLAC%, -1 ) eight

That is because:

Parameter 2:
Number. The first section to return. Assuming parameter 3 is the default (1), a value of 1 for this parameter will return the first section of the text (that is, up to the first comma). A value of 2 would return the second (between the first and second commas); and so on. Negative values can be used to reference sections from the end. -1 references the last section. -2 references the second last section. And so on.

[ColeValleyGirl]

If you use -1 as the position, it gets the last part.

[LindaO]

Ah, penny just dropped the -1 might work. Thanks will try it.

[LindaO]

That worked - thanks so much. You have no idea how long I have spent on this - tried to make something very simple extremely complicated.

Regards,

Linda.

[tatewise]

I know it is not everyone's cup of tea but reading the manual can save a lot of time compared to experimenting.
However, it needs a calm focussed state of mind and we often only resort to the manual when we are panicking.

[LindaO]

Thanks Mike. I spent a lot of my time RTBM when I first started in IT/programming quite a few years ago and had read up on the different text functions in FH before posting. Must having been having a senior moment, as totally misunderstood the TextPart function. Hence the stupid question!

I could have exported the data and manipulated it within a different software package - in this instance it would have been considerably quicker than the route I took - but I am trying to expand my skills in FH, queries in particular, so that as much of the work as possible is done inside FH.

Regards,

Linda.

[tatewise]

You might even like to turn your hand to writing Plugins!