• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

M

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
 
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)
 
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.
 
srinidhi said:
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.
Click to expand...
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.
 
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
 
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

Your second formula will work.

Narayan
 
You must log in or register to reply here.
Back
Top