Extracting an integer from text

[craig-o]

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.

[Sloth]

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