I have the latest version of Excel 365 with all the new functions. 
so I am wondering if extracting text will be easier.
So if cell in column A says "Bank" then the result returned will be the text in column B.
There is two scenarios with the cell named GL in column A where the text string begins with an A. The max length of the text string allowed is 10 characters.
First scenario, is text beginning with a "A" then yymmdd then the last 3 characters reserved for a number. If the last three characters are a number like for example 005 or 010 or 400 then the result returned will be 5, 10 or 400 returned in column C.
The next scenario, If the last character or last two characters are a letter like B or AC, then the text is formatted beginning with an A then mmddyy then the number then the letter or two letters at the end. If the string ends with a two letters like "AC", then I can't use 02 for the month as it would exceed 10 characters. I have to use 2 for the month. The example will makes this clearer.
so I am wondering if extracting text will be easier.So if cell in column A says "Bank" then the result returned will be the text in column B.
There is two scenarios with the cell named GL in column A where the text string begins with an A. The max length of the text string allowed is 10 characters.
First scenario, is text beginning with a "A" then yymmdd then the last 3 characters reserved for a number. If the last three characters are a number like for example 005 or 010 or 400 then the result returned will be 5, 10 or 400 returned in column C.
The next scenario, If the last character or last two characters are a letter like B or AC, then the text is formatted beginning with an A then mmddyy then the number then the letter or two letters at the end. If the string ends with a two letters like "AC", then I can't use 02 for the month as it would exceed 10 characters. I have to use 2 for the month. The example will makes this clearer.
| Book1 | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Result I want | |||||
| 2 | Bank | 5 | 5 | Text Length | ||
| 3 | GL | A221228005 | 5 | 10 | ||
| 4 | GL | A221228010 | 10 | 10 | ||
| 5 | GL | A221228400 | 400 | 10 | ||
| 6 | GL | A12282201A | 1 | 10 | ||
| 7 | GL | A12282202A | 2 | 10 | ||
| 8 | GL | A1228222AC | 2 | 10 | ||
| 9 | GL | A12282210A | 10 | 10 | ||
Sheet1 | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2 | C2 | =IF(A2="Bank",B2) |
| D3:D9 | D3 | =LEN(B3) |
