I have a cell like so , "7.35+.094", is there anyway I can parse this cell with a formula to get "7.35". I dont want to use text to columns, I want to do it within one formula.

My current cell A1 = =Sheet2!A56 which is "7.35+.094", How can I alter this formula to get "7.35"

Any help would be appreciated.


With data in A1, use:

=LEFT(A1,FIND("+",A1)-1) & CHAR(34)

enter image description here

  • Thanks Gary, I meant to give 7.35 without quotes, sorry about that. – Jonathan May 25 '19 at 18:15
  • @Jonathan If there are no double-quotes in A1, then use: =LEFT(A1,FIND("+",A1)-1) – Gary's Student May 25 '19 at 19:07
  • thank you Gary, one last question, what if I wanted to find + or - – Jonathan May 25 '19 at 19:30
  • @Jonathan, if you know there would only ever be one +, and that if it is not found there there will be a - instead you could wrap the current formula in an IFERROR function and then for the error result repeat the formula but replace the + with a - – Forward Ed May 26 '19 at 0:33
  • @ForwardEd Can you show me what you mean? – Jonathan May 29 '19 at 2:09

To build on Gary's Answer to the original question. If there is a + OR a - there take Gary's formula, place it in an IFERROR function and then repeat the formula using a - as the error part of IFERROR as follows:

=IFERROR(LEFT(A1,FIND("+",A1)-1) & CHAR(34),LEFT(A1,FIND("-",A1)-1) & CHAR(34))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.