What formula to use to parse this text column into usable columns for Google Sheets

[geperi]

I have the following text string: {"CAD":1.404,"EUR":0.925,"MXN":25.103,"USD":1,"SGD":1.433,"AUD":1.649,"GBP":0.813}
and I'd like to parse it so that it shows up in a few columns, like this:

CAD

1.404

EUR

0.925

MXN

25.103

USD

1

SGD

1.433

AUD

1.649

GBP

?0.813

What would be the simplest way to achieve this?

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[geperi]

I'm actually working on google sheets for now. can it be done there?

 

[Fluff]

In future when asking questions for Google Sheets you need to make that clear (preferably in the title) & such questions should be posted to the General Discussion & Other Applications section of the board.
I have done that for you this time.

You could have a look at the Split function, but I have no idea how it works.

 

[jdellasala]

Would have been nice to mention that this was for Google Sheets BEFORE I spend a half hour on TWO SOLUTIONS!
Do a google search for "Google Sheets Boards".


This was particularly difficult. First option is to use Power Query. Here's the code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
ReplacedLeftCurly = Table.ReplaceValue(Source,"{","",Replacer.ReplaceText,{"Column1"}),
ReplacedRightCurly = Table.ReplaceValue(ReplacedLeftCurly,"}","",Replacer.ReplaceText,{"Column1"}),
ReplacedDoubleQuotes = Table.ReplaceValue(ReplacedRightCurly,"""","",Replacer.ReplaceText,{"Column1"}),
SplitColumnByColon = Table.SplitColumn(ReplacedDoubleQuotes, "Column1", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}),
TransposedTable = Table.Transpose(SplitColumnByColon),
SplitColumnByComma = Table.ExpandListColumn(Table.TransformColumns(Table.TransformColumnTypes(TransposedTable, {{"Column1", type text}}, "en-US"), {{"Column1", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
TransposedTable1 = Table.Transpose(SplitColumnByComma),
ChangedType = Table.TransformColumnTypes(TransposedTable1,{{"Column1", type text}, {"Column2", type number}, {"Column3", type text}, {"Column4", type number}, {"Column5", type text}, {"Column6", type number}, {"Column7", type text}, {"Column8", Int64.Type}, {"Column9", type text}, {"Column10", type number}, {"Column11", type text}, {"Column12", type number}, {"Column13", type text}, {"Column14", type number}})
in
ChangedType

The second option is using Excel (MS 365) functions:

	{"CAD":1.404,"EUR":0.925,"MXN":25.103,"USD":1,"SGD":1.433,"AUD":1.649,"GBP":0.813}
Formula in B2: =SUBSTITUTE(B1,"{","")	"CAD":1.404,"EUR":0.925,"MXN":25.103,"USD":1,"SGD":1.433,"AUD":1.649,"GBP":0.813}
Formula in B3: =SUBSTITUTE(B2,"}","")	"CAD":1.404,"EUR":0.925,"MXN":25.103,"USD":1,"SGD":1.433,"AUD":1.649,"GBP":0.813
Formula in B4: =SUBSTITUTE(B3,"""","")	CAD:1.404,EUR:0.925,MXN:25.103,USD:1,SGD:1.433,AUD:1.649,GBP:0.813
Formula in B5: =TEXTJOIN("~",,SUBSTITUTE(TEXTSPLIT(B4,",",,,,"~"),":","~"))	CAD~1.404~EUR~0.925~MXN~25.103~USD~1~SGD~1.433~AUD~1.649~GBP~0.813
Formula in B6: =TEXTSPLIT(B5,"~")	CAD	1.404	EUR	0.925	MXN	25.103	USD	1	SGD	1.433	AUD	1.649	GBP	0.813

Hope that helps anyone that comes across this. Maybe it will give you some idea of how to proceed using Sheets.
And yes, all of those Excel Formulas could be rolled into a single cell or LAMBDA, especially if this is an ongoing thing.

 

[Fluff]

For reference in Excel you could simply have used

=TEXTSPLIT(SUBSTITUTE(A2,"""",""),{",",":","{","}"},,1)

 

[jdellasala]

For reference in Excel you could simply have used

=TEXTSPLIT(SUBSTITUTE(A2,"""",""),{",",":","{","}"},,1)

Nice going! Tried to use the trick of defining columns within {} but wasn't getting it to work. I knew there had to be a way!

BTW, geperi, I did try to import the Excel file into Sheets to see if there was something comparable. I did find a SPLIT function, but didn't handle the array of splits like TEXTSPLIT and would need further steps or more SPLITs within the formula to break it up the way Excel did.

 

[geperi]

Sorry about the hassle - haven't been around in ages, and just realized that I can easily add my excel info on here. I upgraded from sheets back to microsoft excel 365 (for MAC).
I tried the Textsplit formula mentioned by Fluff above, but it only spits out the first word ("CAD") and nothing else. What am I missing?

 

[geperi]

I tried to edit to add this but was not able to:
I am not just trying to get CAD. I am trying to get CAD and all the other values, in the following columns. And sometimes the values are 3 digits, sometimes more digits (the numbers next to the currencies)
I believe easiest could be to use Data -> Text to column function in Excel - are you familiar with what parameters I should choose there?

 

[RoryA]

The formula works fine for me on a Mac.