Formula not working properly

[Nanaia]

Would you please provide some feedback on the following formula? It is supposed to be converting the data in cell CSV!AQ3 from feet and inches to decimal inches and doesn't seem to be doing it properly. On my spreadsheet it's showing the result is 4.375 when (I think) it should be 19.625.

The formula was written this way because the CSV the data is pulled from will frequently have spaces randomly put in at the end of it depending on the mood of the data entry person.

I've placed the formula in [ brackets ] and included the number displayed in CSV!AQ3.

Any help is appreciated.


[ =IFERROR(LEFT(CSV!AQ3, FIND("'", CSV!AQ3) - 1), 0) * 12 + SUBSTITUTE(IFERROR(MID(CSV!AQ3, FIND("'",CSV!AQ3) + 1, 8), CSV!AQ3), """", "") ]


CSV!AQ3 contains: 1'-7 5/8"

 

[mole999]

the minus sign between 1' and 7 is causing the issue

 

[Nanaia]

I cannot remove the minus. This would require scouring anywhere from one to over 500 rows of data to remove any that were used. Do you have any suggestions how the formula can be altered to work properly if the data entry person adds a minus?

 

[mole999]

+1 to +3 makes this one work, no idea how that works for other values

 

[Fluff]

How about
IFERROR(LEFT(CSV!AQ3, FIND("'", CSV!AQ3) - 1), 0) * 12 + ABS(SUBSTITUTE(IFERROR(MID(CSV!AQ3, FIND("'",CSV!AQ3) + 1, 8), CSV!AQ3), """", ""))

 

[Nanaia]

Fluff - your revision seems to have done it! Thank you!

 

[Fluff]

You're welcome & thanks for the feedback