Extracting multiple email addresses from a string field

[agerus]

I have a string field that contains only email addresses. There is no limit as the field can contain as many email addresses that will fit in 255 characters. Some only contain one email address.

For those with more than one, each email addresses is separated by a space. The format is first.last@email.com. If the field contains more than one email address, I am looking for a way to extract them one by one and ideally display them in a column. Any suggestions on how this may be possible would be greatly appreciated.

thanks in advance!

 

[fisheromacse]

try:

Replace({table.field}," ",chr(13))

 

[fisheromacse]

you could also create formulas for each possible email occurrence (ie: IF there could be 100 email addresses you would need 100 formulas), place each in it's own section (details A, details B, etc) and then suppress those sections that contain formulas greater than the number of email addresses.
however i think this would be a time-consuming process and one which could be borked quite easily.

 

[agerus]

Thanks for the info. I am not exactly sure what the formula is supposed to do, but it displays only the first email. So if the field contains 5 addresses separated by spaces, it only displays address 1.

 

[pmax9999]

Fisheromcse's formula replaces the spaces between the email addresses with a line feed so as to display them one below the other. To see them all make sure the formula field formatting option of "Can Grow" has been ticked.

Pete.

 

[agerus]

Thanks. that sort of works in that it displays as a column, but it is adding space between each address. when I export it to excel it is not ideal.

Do you have an example of your second suggestion of creating a formula for each possible instance? I do have the ability to limit the number of address to 35. So while it may be tedious to do it, it would only be a single time as I would just reuse the report.

Thanks.

 

[pmax9999]

To get the n^th email address. try this:

If	Ubound(Split({Table.Field}, ' ')) >= n
Then	Split({Table.Field}, ' ')[n]

Repeat for as many times as there could be email addresses replacing the n in the formula with a number. Place all formulas one under the other, splitting the details section into as many as is required. To get it to export to excel neatly, make sure each formula field is at the top of its section, and that the bottom of the section is hard up against the bottom of the field. Any space above or below the formula fields will create surplus rows in the resultant spreadsheet.

Hope this helps.

Pete

 

[pmax9999]

One additional point. If there are 3 addresses in the field and you have allowed for a maximum of 10 (ie with 10 formulas), there will be 7 blank rows. This could be dealt with by formatting each section to suppress if blank.

Pete.