Subfield multiple spaces in field

[tobiasaechter]

Hi,

I am having a field which looks like the below:

Asia Japan
Asia South Korea
America United States
America Canada
Europe Germany

I woud like to split this now into two fields. One for Region and one for Country.

My idea was using subfield like: "Subfield(myfield,' ',1) as Region" & "Subfield(myfield,' ',2) as Country"

But with this I would cut off the "Korea" of South Korea and the "States" of United States.

Does anyone have an idea how to achieve this properly? Does this work with subfield at all?

Thanks

[tresesco]

Identify index of the first space (' ') using index() and cut the string starting from there to end using  mid(), like:

Mid(myfield,Index(myfield, ' '))  as Country

[tobiasaechter · Accepted solution]

Thanks for the idea.

using your script I always had a blank in front of the country names. So I edited it a little bit and now it works perfectly

I am using the following now: Mid(myfield,Index(myfield, ' ')+1)  as Country