# AB12.34 - How to extract "AB", "12" and "34" separately

#### matt-gilbert

##### Member
Hello,
I have been given code numbers similar to above and need to extract the alpha and numeric characters separately. Examples of code numbers as follows;
A1.1
A10.1
A10.10
AB1.1
AB10.1
AB10.10
As you can see each field is not fixed so the formula needs to adapt to suit. I have used the following formula to drop the alpha but this still does not do what I want
=TRIM(REPLACE(A1,1,LEN(TRIM(LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1))),""))
I will need 3 separate formulas so that results are in 3 different cells.
Any help would be appreciated. Thanks. Matt

#### srinidhi

##### Active Member
I did not understand why you want 3 separate formulas, You need to extract the text in one cell & the numbers in another. To extract the text in a cell, use this formula.
=LEFT(A1,MIN(FIND({"0","1","2","3","4","5","6","7","8","9"},A1&"0123456789"))-1)

#### srinidhi

##### Active Member
Ok got your point, sorry I did not read it properly. your formula extract the numbers after that go to data Data>Text to column>Delimted>others, enter . & click on Finish. now you data is spread in 2 cells. Use the formula given above to extract the text.

#### matt-gilbert

##### Member
Ok got your point, sorry I did not read it properly. your formula extract the numbers after that go to data Data>Text to column>Delimted>others, enter . & click on Finish. now you data is spread in 2 cells. Use the formula given above to extract the text.
Thanks srinidhi. I was hoping to set up a template and use formulas rather than have to do a manual text to column each time I do this. If data (AB12.34) is in cell A1, I need formulas in cells B1, C1 and D1 to provide results "AB", "12" and "34" respectively.
Hope this makes sense.

#### NARAYANK991

##### Excel Ninja
Hi Matt ,

Use the following formulae in B1 , C1 and D1 :

B1 : =LEFT(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1)

C1 : =MID(A1,FIND(B1,A1)+LEN(B1),FIND(".",A1)-FIND(B1,A1)-LEN(B1))

D1 : =RIGHT(A1,LEN(A1)-FIND(".",A1))

It is assumed that there will be a period "." in the text in A1 , otherwise the formulae in C1 and D1 will have to be revised.

Narayan

#### srinidhi

##### Active Member
Narayan is truly an excel Ninja. Thanks I learnt a lot today from you.

#### matt-gilbert

##### Member
Thanks Narayan. Exactly what I wanted.

#### Abhijeet R. Joshi

##### Active Member
Narayan,

Can we use the below?

cell B1:- "=LEFT(\$A1,FIND(".",\$A1)-1)"
cell C1:- "=MID(\$A1,FIND(".",\$A1)+1,250)"

#### NARAYANK991

##### Excel Ninja
Hi Abhijeet ,

There are 3 segments in the input data which need to be segregated ; if the input data is ABCD1234.5678 , then the segregated components are :

ABCD - in cell B1

1234 - in cell C1

5678 - in cell D1

Your first formula will club the first two components together and give :

ABCD1234 - in cell B1

Narayan

#### Abhijeet R. Joshi

##### Active Member
Thanks for sharing the knowledge, Narayan...
Cheers!!!

#### Haseeb A

##### Active Member
Also a little shorter in C1 & D1,

C1: =SUBSTITUTE(LEFT(A1,FIND(".",A1)-1),B1,"")

D1: =SUBSTITUTE(A1,B1&C1&".","")