Formatting a Text String

[G_Thompson]

Hi everyone.

I'm driving myself crazy trying to format a text string into the required format!

My workflow is doing a load of stuff but the crux of the issue is this ... I currently have a string that looks like this;

name: site test GT,    address: test address,    city: Newcastle,    state: VIC,    postcode: 2000,    siteContact: test,    siteEmail: test@test.com,    portfolio: Suburban 2,    propertyId: 123456,    fmEmail: test@fm.com,    fmMobile: 01234567,    pmEmail: test@pm.com,    pmMobile: 987465,    propertyType: Leased,    department: CDPP, oneViewClientPropertyID: 12345678

I need it to look like this:

{"name": "site test GT",    "address": "test address",    "city": "Newcastle",    "state": "VIC",    "postcode": "2000",    "siteContact": "test",    "siteEmail": "test@test.com",    "portfolio": "Suburban 2",    "propertyId": "123456",    "fmEmail": "test@fm.com",    "fmMobile": "01234567",    "pmEmail": "test@pm.com",    "pmMobile": "987465",    "propertyType": "Leased",    "department": "CDPP", "oneViewClientPropertyID": "12345678"}

I'm hoping somebody can help with the additions of the " and {}

Many thanks in advance.

[MarqueeCrew]

@G_Thompson 

'{"' + Replace(replace(REGEX_Replace([Input], "\s+", ' '),": ",'": "'),", ",'", "') + '"}'

It will always make 1 space after your commas.

Cheers,

Mark 

[G_Thompson]

@MarqueeCrew 

Many thanks. As you'll see, the below is actually creating { and } at the beginning and end of every field. { actually only needs to be the very first character and } the very last i.e. group the whole string within { and }

[MarqueeCrew]

Hopefully it gets you or others close to the solution. 

sorry, but I'm back to caring for my dogs now.

 Cheers,

 mark

[grazitti_sapna · Accepted solution]

Hi @G_Thompson,

Let me now if this solves your purpose.