Calculated Field to get individual lines in Text Field

[DesiLogi]

Hello,

I have a Text field (64000) that is used to hold addresses with html markup. I need to use calculated fields to get each line's value for use in importing to another app that requires the address to be broken down to individual fields. 

For example, the value in the text field might be:

Acme Building <br />

22 Main Street <br />

Suite 33 <br />

Atlanta, GA 30308

I need it to take each line and put it in a calculated field:

Calculated Field 1 = "Acme Building"

Calculated Field 2 = "22 Main Street"

Calculated Field 3 = "Suite 33"

Calculated Field 4 = "Atlanta, GA 30308"

There will always be a <br /> to break up the lines so I was thinking that might be used in the formula to delineate which lines are which. I have no idea of what the formulas would look like, though. Does anyone know how to do this? Many thanks!

 

[MayMusic]

You can use combination of SUBSTRING and CHARINDEX function to get the values. For instance if the text is in a field called add.

First word before first break:
 

SUBSTRING( '[@field:add]',  0,Charindex( '<br />','[@calcfield:4]'))

Second word:7 is the length of break : <br />
 

SUBSTRING( '[@field:add]',  Len(SUBSTRING( '[@field:add]',  0,Charindex( '<br />','[@field:add]'))) + 7, Charindex('<br />',
SUBSTRING( '[@field:add]',  Len(SUBSTRING( '[@field:add]',  1,Charindex( '<br />','[@field:add]'))) + 7, LEN('[@field:add]'))
)
)

Third one:
 

SUBSTRING('[@field:add]',
 Charindex('<br />',

SUBSTRING( '[@field:add]',  Len(SUBSTRING( '[@field:add]',  1,Charindex( '<br />','[@field:add]'))) +14, Charindex('<br />', LEN('[@field:add]')))
, LEN('[@field:add]')
) +19
 , LEN('[@field:add]'))

and so on ...

[DesiLogi]

Hi,

Thanks for posting this but I'm not seeing this get the results I laid out and I'm not sure why. What's the calcfield:4 in your code for? In the initial query I put that I wanted to use calculated fields to get the results, so I need the code for each calculated field. I.E. calcfield:4 should show the words after the last <br />  (which would be City. State Zipcode). 

So what I need is formulas for:

Calcfield:1 to show whatever text is before the 1st <br />

Calcfield:2 to show whatever text is between the 1st <br /> and the 2nd <br />

Calcfield:3 to show whatever text is between the 2nd <br /> and the 3rd <br />

Calcfield:4 to show whatever text is after the last<br /> 

I'm not sure what the 7 means in your code- the values will never have a fixed character length as they are addresses which will always be different.  I think you've laid out the basic way to work this out but I can't seem to get it to do, in calculated fields, exactly what I've laid out above. It'd be great if you can look this over. Many thanks!

EDIT: I got this to work for the 2nd line (calcfield:2 in my example)

SUBSTRING( '[@field:add]',  Len(SUBSTRING( '[@field:add]',  0,Charindex( '<br />','[@field:add]'))) + 7, Charindex('<br />',
SUBSTRING( '[@field:add]',  Len(SUBSTRING( '[@field:add]',  1,Charindex( '<br />','[@field:add]'))) + 7, LEN('[@field:Ship_To_Entity]'))
)
)

When I try the 3rd line code it splits all the text up and doesn't choose between the <br /> parts. Since the 2nd one works it must be close to working for the other lines with a few tweaks.