Trimming fields to space delimiter

[amrnewman]

Hi.
Using Crystal XI

I have a string address field that contains some street names and some street names with house numbers e.g

123 Example Street
123 Example Two Street
Example Three Street
123 Street
Example Street

I need to be able to trim this to be show only the street name (i.e. no numbers because of data protection) but am struggling with trimming to a space delimiter because of the different combinations of addresses. I have not found an example of more than three street names plus numbers. Using the example above I want to be able to show

Example Street
Example Two Street
Example Three Street
Street
Example Street

but cannot get a formula to work. I have tried tweaking some of the options here but cannot get anything to work. I hope this makes sense. Please help!

Thanks in advance, Ade

 

[IanWaterman]

Try this formula, it assume number will always be a beginning. Bit clunky but it works.

@Street
If Isnumeric(Trim(split('123 Example Two Street', " ")[1])) then
mid ('123 Example Two Street',length(split('123 Example Two Street', " ")[1])+1, 30) else
'123 Example Two Street'

Replace '123 Example Two Street' with your address field.

Ian

 

[amrnewman]

Ian - you are officially a hero to my lettings manager now - it worked.!

THanks for a swift reply. Regards, Adrian