Formula to Pull Just Words or $ Amounts into a Report

The values are still text. The 1111 has a hidden ' on the front. So this formula

=VALUE(SUBSTITUTE(LEFT([TEST Number]@row, FIND(" - ", [TEST Number]@row) - 1), "'", ""))

and this one to remove the $symbol to make it a value.

=VALUE(SUBSTITUTE(RIGHT([TEST Number]@row, LEN([TEST Number]@row) - FIND("- $", [TEST Number]@row, FIND("-", [TEST Number]@row) + 1) - 1), "$", ""))

Use the column formatting to add the $ back to the column so it remains a value but shows the $

I am sorry I did not catch this before. I am a little distracted.

All comments

the Text column has this formula

=LEFT([TEST Number]@row, FIND("-", [TEST Number]@row) - 1)

The Middle Column has this formula

=MID([TEST Number]@row, FIND("-", [TEST Number]@row) + 1, FIND("-", [TEST Number]@row, FIND("-", [TEST Number]@row) + 1) - FIND("-", [TEST Number]@row) - 1)

and the Number column has this Formula

=RIGHT([TEST Number]@row, LEN([TEST Number]@row) - FIND("-", [TEST Number]@row, FIND("-", [TEST Number]@row) + 1) - 1)

@Mark.poole Thanks! that worked. I will make a helper sheet and then from there pull into reporting.

@Mark.poole if I wanted to make a sum out of the Volume or $ column what would the formula be? I don't think it likes that there are formulas in that column, so it won't add and keeps returning a value of 0. Thanks

The issue would actually be that it is pulling a text value. The way to change it to a numerical value is to do is to add the Value() function to the formula. on any spot that that should be a numerical value. In the example above that would be the first and third columns.

=VALUE(LEFT([TEST Number]@row, FIND("-", [TEST Number]@row) - 1))

and =VALUE(RIGHT([TEST Number]@row, LEN([TEST Number]@row) - FIND("-", [TEST Number]@row, FIND("-", [TEST Number]@row) + 1) - 1))

once this is done you should be able to sum them.

@Mark.poole Thanks! I added that to both columns and it is coming back as Invalid Value.

I copied the formula like you wrote and just changed out the Test Number with Primary Column and it won't work for just pulling into a helper sheet.

=VALUE(LEFT([Primary Column]@row, FIND("-", [Primary Column]@row) - 1))

=VALUE(RIGHT([Primary Column]@row, LEN([Primary Column]@row) - FIND("-", [Primary Column]@row, FIND("-", [Primary Column]@row) + 1) - 1))

Unsure what is wrong as I am not so great with formulas. Thanks

The values are still text. The 1111 has a hidden ' on the front. So this formula

=VALUE(SUBSTITUTE(LEFT([TEST Number]@row, FIND(" - ", [TEST Number]@row) - 1), "'", ""))

and this one to remove the $symbol to make it a value.

=VALUE(SUBSTITUTE(RIGHT([TEST Number]@row, LEN([TEST Number]@row) - FIND("- $", [TEST Number]@row, FIND("-", [TEST Number]@row) + 1) - 1), "$", ""))

Use the column formatting to add the $ back to the column so it remains a value but shows the $

I am sorry I did not catch this before. I am a little distracted.