I've seen the other articles that answer very similar questions but those formulas do not work for me, I needed to post a new question.

I have the following column values:

  • Leadership;#4#
  • Character;#2#
  • Communication;#5#
  • Teamwork;#3#

I need to extract ONLY the number from each of those column values. I tried to combine RIGHT & LEFT conditions with no luck. I am on a time crunch and desperate!! Can anyone help??

1 Answer 1


If these are four separate values and the number is a single digit:


You can extract the number using:

=LEFT( RIGHT( [TheColumn] ,2) ,1)

enter image description here

If there are many digits then use:

=MID(LEFT(TheColumn,LEN(TheColumn)-1), FIND("#",TheColumn)+1,999)

enter image description here

  • This is exactly what I needed, THANK YOU SO MUCH!
    – seaandtea
    Commented Jul 16, 2019 at 20:11
  • Mike - I took these columns and created a column for the average using =AVERAGE. Do you know how I could avoid getting the #DIV/0 error message? All of my columns have a value in them. I attempted to use ISERROR but no luck.
    – seaandtea
    Commented Jul 16, 2019 at 23:15
  • Are your Calculated Columns set to return a Number? Commented Jul 16, 2019 at 23:25
  • I changed all of the calculated columns to return a number and no luck still. Going to start fresh tomorrow
    – seaandtea
    Commented Jul 17, 2019 at 0:59
  • Change your Average formula like this: =AVERAGE( 0 + column1, 0 + column2 ) Commented Jul 17, 2019 at 4:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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