# Extract Text using one formula



## pto160 (Jan 5, 2023)

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.

Book1ABCD1Result I want2Bank55 Text Length3GLA2212280055104GLA22122801010105GLA221228400400106GLA12282201A1107GLA12282202A2108GLA1228222AC2109GLA12282210A1010Sheet1Cell FormulasRangeFormulaC2C2=IF(A2="Bank",B2)D3:D9D3=LEN(B3)


----------



## Peter_SSs (Jan 5, 2023)

Is this what you are after?

23 01 05.xlsmABC1Result I want2Bank553GLA22122800554GLA221228010105GLA2212284004006GLA12282201A17GLA12282202A28GLA1228222AC29GLA12282210A10ExtractCell FormulasRangeFormulaC2:C9C2=IF(A2="Bank",B2,IF(ISNUMBER(RIGHT(B2,1)+0),RIGHT(B2,3)+0,MID(B2,8,1+ISNUMBER(MID(B2,9,1)+0))+0))


----------



## Phuoc (Jan 5, 2023)

Or try

Book1ABC1Result I want2Bank553GLA22122800554GLA221228010105GLA2212284004006GLA12282201A17GLA12282202A28GLA1228222AC29GLA12282210A10Sheet1Cell FormulasRangeFormulaC2:C9C2=LOOKUP(999,--LEFT(RIGHT(B2,3),{1,2,3}))


----------



## kvsrinivasamurthy (Jan 5, 2023)

Try,

```
=IF(A2="Bank",B2,VALUE(TEXTJOIN("",TRUE,IF(ISNUMBER(MID(B2,{8,9,10},1)+0),MID(B2,{8,9,10},1)+0,""))))
```


----------



## Fluff (Jan 5, 2023)

Another option
Fluff.xlsmABC1Result I want2Bank553GLA22122800554GLA221228010105GLA2212284004006GLA12282201A17GLA12282202A28GLA1228222AC29GLA12282210A10DataCell FormulasRangeFormulaC2:C9C2=IF(A2="Bank",B2,TEXTBEFORE(RIGHT(B2,3),CHAR(SEQUENCE(,26,65)),,,1)+0)


----------



## pto160 (Jan 5, 2023)

Thanks so much to everyone. These solutions are absolutely fantastic. This is going to save me so much time.


----------



## Fluff (Jan 5, 2023)

Glad we could help & thanks for the feedback.


----------



## pto160 (Jan 5, 2023)

I am not sure if I need to set up a new thread but the data stays the same. In column D, I would like to see if the last character or 2nd last last character ends in for example A or AC then it is text otherwise it is a number.
Book1ABCD1Result I wantIs Text2Bank55No3GLA2212280055No4GLA22122801010No5GLA221228400400No6GLA12282201A1Yes7GLA12282202A2Yes8GLA1228222AC2Yes9GLA12282210A10Yes10GLA12282210BA10YesSheet1


----------



## Sufiyan97 (Jan 5, 2023)

Try

Book5ABCDE1Result I wantIs Text2Bank55No3GLA2212280055No4GLA22122801010No5GLA221228400400No6GLA12282201A1Yes7GLA12282202A2Yes8GLA1228222AC2Yes9GLA12282210A10Yes10GLA12282210BA10Yes1112Sheet1Cell FormulasRangeFormulaD2:D10D2=IF(ISNUMBER(RIGHT(B2)+0),"No","Yes")


----------



## pto160 (Jan 5, 2023)

That works. Thanks so much.


----------



## pto160 (Jan 5, 2023)

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.

Book1ABCD1Result I want2Bank55 Text Length3GLA2212280055104GLA22122801010105GLA221228400400106GLA12282201A1107GLA12282202A2108GLA1228222AC2109GLA12282210A1010Sheet1Cell FormulasRangeFormulaC2C2=IF(A2="Bank",B2)D3:D9D3=LEN(B3)


----------



## Sufiyan97 (Jan 5, 2023)

You're welcome!


----------

