* Extracting text from a string

Questions regarding use of any Version of Family Historian. Please ensure you have set your Version of Family Historian in your Profile. If your question fits in one of these subject-specific sub-forums, please ask it there.
Post Reply
avatar
LindaO
Gold
Posts: 15
Joined: 11 Apr 2010 13:11
Family Historian: V7

Extracting text from a string

Post by 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
User avatar
ColeValleyGirl
Megastar
Posts: 5465
Joined: 28 Dec 2005 22:02
Family Historian: V7
Location: Cirencester, Gloucestershire
Contact:

Re: Extracting text from a string

Post by ColeValleyGirl »

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

https://www.family-historian.co.uk/help ... tpart.html
avatar
LindaO
Gold
Posts: 15
Joined: 11 Apr 2010 13:11
Family Historian: V7

Re: Extracting text from a string

Post by 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.
User avatar
tatewise
Megastar
Posts: 28342
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Extracting text from a string

Post by 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.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
User avatar
ColeValleyGirl
Megastar
Posts: 5465
Joined: 28 Dec 2005 22:02
Family Historian: V7
Location: Cirencester, Gloucestershire
Contact:

Re: Extracting text from a string

Post by ColeValleyGirl »

If you use -1 as the position, it gets the last part.
avatar
LindaO
Gold
Posts: 15
Joined: 11 Apr 2010 13:11
Family Historian: V7

Re: Extracting text from a string

Post by LindaO »

Ah, penny just dropped the -1 might work. Thanks will try it.
avatar
LindaO
Gold
Posts: 15
Joined: 11 Apr 2010 13:11
Family Historian: V7

Re: Extracting text from a string

Post by 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.
User avatar
tatewise
Megastar
Posts: 28342
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Extracting text from a string

Post by 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.
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
avatar
LindaO
Gold
Posts: 15
Joined: 11 Apr 2010 13:11
Family Historian: V7

Re: Extracting text from a string

Post by 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.
User avatar
tatewise
Megastar
Posts: 28342
Joined: 25 May 2010 11:00
Family Historian: V7
Location: Torbay, Devon, UK
Contact:

Re: Extracting text from a string

Post by tatewise »

You might even like to turn your hand to writing Plugins!
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry
Post Reply