Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I have text data in one column (Japanese address in Alphabet), and I need to extract some data from the right.
Example:
ColumnA
123-52, ZZZZ, ZZZZZZ City, ZZZZZZ Prefecture, 1234567, JAPAN
and what I need to extract is "ZZZZZZ Prefecture, 1234567, JAPAN"
in other words, find the third "," from the right, and extract all the numbers and characters on the right hand side of that.
It might be a bit easier to find third comma from the left but I cannot use it since it could be third or fourth from the left depending on the format of the address, but it is always the third from right.
Thanks in advance !
Solved! Go to Solution.
Hi @Anonymous,
Please go through the below Screen shot, will solve your problem.
If you find this as solution "Accept as solution" else let me know Thanks.
Formula:- NewColumn = SUBSTITUTE(REPLACE(SplitExample[Column1],1,7,""),",","")
Hi @Anonymous,
Please go through the below Screen shot, will solve your problem.
If you find this as solution "Accept as solution" else let me know Thanks.
Formula:- NewColumn = SUBSTITUTE(REPLACE(SplitExample[Column1],1,7,""),",","")
There should be some coniditon based on which you are showing after last 2 commas or after last 3 commas. What is that?
@Anonymous
How about splitting the Column from Query Editor/Power Query using "Comma" as a delimiter
Then you can recombine the last three columns