Separate Number and Text

[zwyne]

Hello Experts,

PFA

In attached file I have column called Value Local Currency which is a mixed column. So I am trying to separate value and text but I am getting wrong results. I have used the following statement as below:

KeepChar([Value Local Currency],'0123456789') AS number,
PurgeChar([Value Local Currency],'0123456789') AS text

Please assist.

Thanks

[avkeep01]

Hi Bafana,

Try SUBFIELD([Value Local Currency],' ZAR',1) AS Value

That will retrevie the part before " ZAR", which is the value.

[tunoi]

try these:

right([Value Local Currency],len([Value Local Currency]) - index([Value Local Currency], ' ')) as Currency

and

num(left([Value Local Currency],index([Value Local Currency], ' '))) as [Expected Value]

[neelamsaroha157]

Hi Bafana,

Check this out.

Let me know if you find it helpful.

Thanks

[surendraj · Accepted solution]

Simply You can

subfield([Value Local Currency],' ',1) as Number

subfield([Value Local Currency],' ',2) as Text

[zwyne]

Thank you everyone for the assistance.