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??


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 Jul 16 '19 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 Jul 16 '19 at 23:15
  • Are your Calculated Columns set to return a Number? – Mike Smith - MCT - MVP Jul 16 '19 at 23:25
  • I changed all of the calculated columns to return a number and no luck still. Going to start fresh tomorrow – seaandtea Jul 17 '19 at 0:59
  • Change your Average formula like this: =AVERAGE( 0 + column1, 0 + column2 ) – Mike Smith - MCT - MVP Jul 17 '19 at 4:05

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.