* Extracting text from a string
Extracting text from a string
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
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
- Megastar
- Posts: 5465
- Joined: 28 Dec 2005 22:02
- Family Historian: V7
- Location: Cirencester, Gloucestershire
- Contact:
Re: Extracting text from a string
Have you tried using TextPart -1 to get the text you want?
https://www.family-historian.co.uk/help ... tpart.html
https://www.family-historian.co.uk/help ... tpart.html
Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history
Re: Extracting text from a string
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.
Regards,
Linda.
- tatewise
- Megastar
- Posts: 28342
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Re: Extracting text from a string
The help page for the =TextPart function gives an example of that:
That is because: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
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
- ColeValleyGirl
- Megastar
- Posts: 5465
- Joined: 28 Dec 2005 22:02
- Family Historian: V7
- Location: Cirencester, Gloucestershire
- Contact:
Re: Extracting text from a string
If you use -1 as the position, it gets the last part.
Helen Wright
ColeValleyGirl's family history
ColeValleyGirl's family history
Re: Extracting text from a string
Ah, penny just dropped the -1 might work. Thanks will try it.
Re: Extracting text from a string
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.
Regards,
Linda.
- tatewise
- Megastar
- Posts: 28342
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Re: Extracting text from a string
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.
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
Re: Extracting text from a string
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.
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
- Megastar
- Posts: 28342
- Joined: 25 May 2010 11:00
- Family Historian: V7
- Location: Torbay, Devon, UK
- Contact:
Re: Extracting text from a string
You might even like to turn your hand to writing Plugins!
Mike Tate ~ researching the Tate and Scott family history ~ tatewise ancestry