Can you convert the result of a Mid formula to an amount?

Jessica Reser

Hi, I'm trying to build a report that sums the amount which is being pulled out of a line of data with the Mid formula.

The formula itself works fine but the report will only give me a count. I believe because it doesn't see the result of the formula as a number. Is there any way I can change this to give me a sum?

Leibel S

@Jessica Reser

Place your MID formula inside a =VALUE(MID()) formula. This will convert into a number.

Jessica Reser

@Leibel S

Now I'm getting invalid value

Leibel S

@Jessica Reser

How do you decide on the character numbers in your MID function?

Is the data in the Primary column consistent?

For the value function to work all the characters being brought in need to be of a number type. In the example you highlighted the first character is a space, which results in an error.

You could just change it to:

=VALUE(MID([Primary Column]@row, 44, 9))

But how does that effect the rest of the rows?

If you need it to work with other setups (if sometimes the number does start from character 43), then you can maybe add in a SUBSTITUTE function to get rid of spaces:

=VALUE(SUBSTITUTE(MID([Primary Column]@row, 43, 9)," ",""))

Jessica Reser

@Leibel S

Thank you so much for your help!

I actually ended up leaving the Mid function the way I had it and added a column with just =VALUE(AMT@row)

That seems to be doing what I need it to do.

I can't change the format of the Primary Column as it's data coming out of a legacy system