Formula to Pull Just Words or $ Amounts into a Report

Jenny A.

Not sure if this is possible - If there is a cell that has words and then a dollar amount, is there a formula that can be used to pull only the dollar amount into a report and then on the opposite side be able to pull the first part and not the $ amount?

Ex: 11111 - Test Item - $100

The format for the example could slightly vary, or there could be multiple items and $ amounts.

Thanks

Mark.poole

@Jenny A.

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.

Mark.poole

@Jenny A.

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)

Jenny A.

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

Jenny A.

@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

Mark.poole

@Jenny A.

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.

Jenny A.

@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

Mark.poole

@Jenny A.

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.

Jenny A.

@Mark.poole no worries. Those formula's worked and I can now add the dollars. Thanks