Extracting an integer from text
 

Greetings all. I’ve been asked to add some functionality to an Excel
spreadsheet and I’m not exactly sure how to get one of the requests
accomplished. Hopefully someone here smarter than I (that should
include a lot of people) can give me some direction.

What I’ve got is a series of columns that need to either increment in
value, or be appended with an X based on some conditions. What we are
doing is tracking event completion. If someone doesn’t attempt or
complete an event that is scheduled, the event number is carried
forward into the next column and appended with an X. If this happens
again, an additional X is appended. I have this portion of the formula
working so that it adds my X (or Xs) using CONCATENATE.

My problem arises when a column containing, for example 2X, needs to be
incremented to a 3. In this instance, someone completed the second event
and needs to be tasked with event 3 and their performance measured. I’m
not sure how to extract the leading integer off of the X (or maybe Xs)
and increment the number.

Hopefully this all makes sense.

Thanks for any advice.


--
craig-o
------------------------------------------------------------------------
craig-o's Profile: http://www.excelforum.com/member.php...o&userid=27588
View this thread: http://www.excelforum.com/showthread...hreadid=483634

Extracting an integer from text
 

A1: 2XX
A2: =LEFT(A1,FIND("X",A1)-1)
A2: will result in 2

This works for any number of digits, and any number of X's.

If you want to add 1 just use
=LEFT(A1,FIND("X",A1)-1)+1

If you want to keep the X (to result in 3XX) use
=(LEFT(A1,FIND("X",A1)-1)+1)&(RIGHT(A1,LEN(A1)-FIND("X",A1)+1))

The & symbol does the same thing as Concotanate, just easier.

"craig-o" wrote:


Greetings all. Ive been asked to add some functionality to an Excel
spreadsheet and Im not exactly sure how to get one of the requests
accomplished. Hopefully someone here smarter than I (that should
include a lot of people) can give me some direction.

What Ive got is a series of columns that need to either increment in
value, or be appended with an X based on some conditions. What we are
doing is tracking event completion. If someone doesnt attempt or
complete an event that is scheduled, the event number is carried
forward into the next column and appended with an X. If this happens
again, an additional X is appended. I have this portion of the formula
working so that it adds my X (or Xs) using CONCATENATE.

My problem arises when a column containing, for example 2X, needs to be
incremented to a 3. In this instance, someone completed the second event
and needs to be tasked with event 3 and their performance measured. Im
not sure how to extract the leading integer off of the X (or maybe Xs)
and increment the number.

Hopefully this all makes sense.

Thanks for any advice.


--
craig-o
------------------------------------------------------------------------
craig-o's Profile: http://www.excelforum.com/member.php...o&userid=27588
View this thread: http://www.excelforum.com/showthread...hreadid=483634