Finding a character inside a string in Excel

Question

I want to remove all the characters from a string expect whatever character is between a certain set of characters. So for example I have the input of Grade:2/2014-2015 and I want the output of just the grade, 2.

I'm thinking that I need to use the FIND function to grab whatever is between the : and the / , this also needs to work with double characters such 10 however I believe that it would work so long as the defining values with the FIND function are correct.

Unfortunately I am totally lost on this when using the FIND function however if there is another function that would work better I could probably figure it out myself if I knew what function.

Does every record start with "Grade:"?

Chase Ernst
– Chase Ernst

2014-07-23 14:34:27 +00:00
Commented Jul 23, 2014 at 14:34 — Chase Ernst
– Chase Ernst, Commented Jul 23, 2014 at 14:34
Yes, every record will follow the same format

user3328187
– user3328187

2014-07-23 14:37:04 +00:00
Commented Jul 23, 2014 at 14:37 — user3328187
– user3328187, Commented Jul 23, 2014 at 14:37

Holloway · Accepted Answer · 2014-07-23 15:16:48Z

2

It's not particularly elegant but =MID(A1,FIND(":",A1)+1,FIND("/",A1) - FIND(":",A1) - 1) would work.

MID takes start and length,FIND returns the index of a given character.

Edit: As pointed out, "Grade:" is fixed length so the following would work just as well: =MID(A1,7,FIND("/",A1) - 7)

edited Jul 23, 2014 at 15:16

answered Jul 23, 2014 at 15:00

Holloway

7,5471 gold badge29 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

InContext Over a year ago

+1 most dynamic answer although could immediately substitute the Grade: which is static

Chase Ernst · Accepted Answer · 2014-07-23 14:41:07Z

1

You could use LEFT() to remove "Grade:"

And then use and then use LEFTB() to remove the year.

Look at this link here. This is the way I would go about it.

answered Jul 23, 2014 at 14:41

Chase Ernst

1,1552 gold badges26 silver badges54 bronze badges

Comments

Igor Pejic · Accepted Answer · 2014-07-23 14:51:08Z

0

=SUBSTITUTE(SUBSTITUTE(C4, "Grade:", ""), "/2014-2015", "")

where C4 is the name of your cell.

edited Jul 23, 2014 at 14:51

answered Jul 23, 2014 at 14:34

Igor Pejic

3,7061 gold badge17 silver badges32 bronze badges

6 Comments

user3328187 Over a year ago

I think you forgot to attach the screenshot however I do need this to be formula because the data will change and I want it to output the grade automatically.

user3328187 Over a year ago

Thank you very much! I also learned a new function today haha

Igor Pejic Over a year ago

No problem, mark the answer as right so the question doesn't appear in the unanswered questions anymore.

InContext Over a year ago

this will not work when the year changes to 2015-2016 and / or you are using past year ranges.

Igor Pejic Over a year ago

citation: Yes, every record will follow the same format

|

Collectives™ on Stack Overflow

Finding a character inside a string in Excel

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related