MID Function form Excel

[ZoeM]

Hi Community.

I have a rather weirdly simple scenario, I have a field with three numbers and I want to split them up into three separate columns, like text to columns in Excel. 

The first and last numbers I used:

ToNumber(Left(ToString([Scale]), 1))

ToNumber(Right(ToString([Scale]), 1))

But the middle number I cannot extrapolate. I tired the Substring(String,start,length) formula but I got the infamous error:  Type Mismatch, number provided...

Any help rendered will be truly appreciated.

[yalmar_m]

Hi @ZoeM,

DId you try to use the RegEx tool?

Attached in an example that might help you out.

Best,

Yalmar

[Thableaus · Accepted solution]

Hi @ZoeM 

Use RegEX tool in Parse mode with this expression (\d)(\d)(\d)

Much easier.

Cheers,

[ZoeM]

Thanks for the solutions guys!

And quick response too!

[ZoeM]

So I need to join the new three created fields back to the data model and when I use the join tool I get the error string fields can only join to other string fields etc. 

My original field that I need to parse is a double field. Any advice on how to add my new fields to the model?

[yalmar_m]

You might want to append them, instead of joining them.

Can you show us your workflow?

[ZoeM]

I found a workaround, not sure if it is the best solution but it seems to be working just right for what I need. 

From the source data, I added a select tool and redirected my double field and another string field. I parsed the double field, and used the string field to join to the rest of the data model.