Extract the final word in a string in Excel (variable length) 2

[Alboppo1]

Hi,
I'm trying to extract the final word in a string of text - This could be of a variable length but there are consistently special characters in front of the the word and after the word. I'm so close but can't quite get it right - Any help would be greatly appreciated.
Sample string:
[{"colour":"Silver/Beige","texture":"Brittle","material":"mastic"}] From this I would need to extract mastic
[{"colour":"Brown","texture":"Hard","material":"plasterboard"}] From this I would need to extract plasterboard

I have got this formula so far: =TRIM(MID(AD2,FIND("#",SUBSTITUTE(AD2,":","#",3))+2,255)) this gives me a result of mastic"}]

 

[Andrzejek]

Is that what you have?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[SkipVought]

Just making a guess--you need to end up with...
[pre]
colour texture material
Silver/Beige Brittle mastic
Brown Hard Plasterboard
[/pre]

If so, use Find/Replace to get rid of
"[", "]"
"{", "}" and """

Then use Data>Text to columns > Delimited on COMMA, to parse color, texture, material.

Finally use Data>Text to columns > Delimited on COLON and DO NOT IMPORT COLUMN 1, for each column: color, texture, material.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[mintjulep]

Your data looks suspiciously like a python dictionary.

VBA supports dictionaries, and collections, which are similar. It should not be difficult to parse your data in VBA into a dictionary or collection.

That would give you much more functionality than you ask for however.

 

[strongm]

Looks even more suspiciously like a JSON array ...

 

[Alboppo1]

Thanks very much everyone, much appreciated.
It is JSON and is provided to me on a weekly basis from another business.
As I have to do this each week, I was hoping for a formula to pull out the last word so I can drop the data in my template and generate the report I need without the need to find and replace (if possible - and only because I'm lazy!).
I literally just need the last word in the string and can ignore the rest of it.

 

[Andrzejek]

If you are truly lazy, you should ask the ‘another business’ to give you just what you need in the format you want. This way you wouldn’t have to do anything. That is lazy to me. Or is it 'efficient'...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[mintjulep]

Depending on a bunch of things that you don't control, and the "another business" may or may not control, the order of the elements may or may not be the same in the future.

Today you need the last word, but in the future it could become the middle word.

A fundamental concept of JSON is name: value, so that you can ask for and get what you want without worrying about the order.

I didn't know about this before, but might make use of it in the future now that I do:

https://support.microsoft.com/en-us...er-query-7436916b-210a-4299-83dd-8531a1d5e945

 

[mikrom]

Other option would be to use this class VbsJson

https://github.com/eklam/VbsJson

Create class module and then write function for getting JSON value for specific attribute

Tried this

Function json_value(json_array_str, json_attribute)
    'input is json array string with one json element, e.g.:
    '[{"colour":"Silver/Beige","texture":"Brittle","material":"mastic"}]
    
    'extract json from json array with one json element
    json_str = Replace(json_array_str, "[", "")
    json_str = Replace(json_str, "]", "")
    
    Set vbj = New VbsJson
    Set json = vbj.Decode(json_str)
    json_value = json(json_attribute)
End Function

works good

 

[strongm]

Or your can leverage JScript's ability to natively parse JSON ...

e,g

https://www.tek-tips.com/viewthread.cfm?qid=1789285

 

[mikrom]

This works well too: VBA-JSON

https://github.com/VBA-tools/VBA-JSON

strongm, the solution with object ScriptControl does not work in my Excel365. I get en error like described here:

https://answers.microsoft.com/en-us...throwing/600cc674-733a-4481-9cd1-1f836df9be84

 

[strongm]

>Excel365

64bit or 32bit? Script control only comes in a 32bit version

 

[mikrom]

I have 64 bit version

 

[strongm]

Yep, that would cause the error you are seeing

 

[mikrom]

Then there is no go with calling JavaScript in 64-bit Excel.
It's sad that MS Office doesn't really support scripting in languages other than VBA, when LibreOffice supposedly supports 4 scripting languages:

https://help.libreoffice.org/6.1/en-US/text/shared/guide/scripting.html

 

[strongm]

>Then there is no go with calling JavaScript in 64-bit Excel.

Well, there is a 3rd party 64bit MSScript control clone. Not tried it myself, though, as I currently prefe to stick with 32-bit Office

 

[Alboppo1]

Thank you for all your help @mikrom your solution worked a treat!

 

[mikrom]

> I currently prefer to stick with 32-bit Office
You're right, not the most modern is always the best. Unfortunately I have no choice, I got my version of windows+office from my employer.