Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas dealing with text data | Excel Worksheet Functions | |||
Text Field | Excel Discussion (Misc queries) | |||
Extracting the number from a text | Excel Discussion (Misc queries) | |||
extracting text only | Excel Worksheet Functions | |||
EXTRACTING TEXT | Excel Discussion (Misc queries) |