0

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

Improve this question

1 Answer 1

Reset to default
1

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

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

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

Improve this answer
7
  • This is exactly what I needed, THANK YOU SO MUCH!
    – seaandtea
    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
    Jul 16, 2019 at 23:15
  • Are your Calculated Columns set to return a Number?
    – Mike Smith - MCT
    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
    Jul 17, 2019 at 0:59
  • Change your Average formula like this: =AVERAGE( 0 + column1, 0 + column2 )
    – Mike Smith - MCT
    Jul 17, 2019 at 4:05

Your Answer

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

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