Extracting the address only to fit uptill 30 characters but spitting it after the last spa

[ManishaChand]

I need to split the address column on the basis of the character limit(30) but I need to extract the word after the last space for it to make sense, for example if the address is "15677 THREE FATHOMS BANK DRIVE LANES ROAD" 

I need the data till 15677 THREE FATHOMS BANK DRIVE in one column and  LANES ROAD in another column. I am thinking of placing an index on the 30th character and trimming on the basis on the last space with respect to it. How can I build a logic around it?

[Wesley Mendonca]

You can use REGEX TOOL, and configurate to separate, using REGEX: (.{1,30})(.+)

[KGT]

If you do the above to create a new column, you can find the last space using Length([REGEXFieldName],Findstring(ReverseString([REGEXFieldName])," ")) and the data you're after by wrapping the above with Left([Address],formula above) and then use that to create another field for the leftover: Trim(Replace([Address],[dataIwantedfield],""))

You may need to use Trim() a couple times in there and maybe a -1 to adjust for the 0-based array return...

[ManishaChand]

is there any way I can find the 30th character and from there do reverse string to find the first space and trim the word from there to a separate column.

[Wesley Mendonca]

Can you provide one example ? 

[KGT]

You're best to just Left([Field],30) rather than finding the 30th character so you don't have to deal with the 30th character being an A and that appearing 7 times. You can separate the below into multiple formulas if you want to understand it better.

So, let's say your field is called [Address], the formula would be:

• Left([Address],Findstring(ReverseString(Left([Address],30))," "))

And then to get the left over, it would be:

• Replace([Address], [FieldAbove],"")

Separated formulas:

• Field1 = Left([Address],30)
• Field2 = Findstring(ReverseString([Field1])," ")
• Field3 = Left([Address],[Field2])
• Field4 = Replace([Address],[Field3],"")

Field3/4 will be your results

[flying008]

Hi, @ManishaChand 

Could you please upload a table with input data and output data as your want ?

[Buddhi_DB]

Please anyone can help me to resolve the below post. I'm in urgent situation. 

https://community.alteryx.com/t5/Alteryx-Designer-Desktop-Discussions/Weighted-Average-Cost-computat...