Splitting Alpha Numeric characters in Cell reference

[fkwilliams@gmail.com]

Hello Guys


Looking for help:

I am trying to split the characters to cell references so that I can separate out just the row number.

e.g A12 - to - A & 12

AB12- to - AB & 12

AB143 - to - AB & 143


The number of the alpha characters & the Numeric characters can differ so cannot use (LEFT and RIGHT formulae)


Many Thanks for your help in advance.

 

[Faseeh]

Hi fkwilliams,


Assuming that you have entered data in A1 and you want alphabatic part in B1 and nummeric part in C1, following formulas will work:


Enter in B1 : =LEFT(A1,LEN(A1)-LEN(ROW(INDIRECT(A1))))

Enter in C1 : =ROW(INDIRECT(A1))


Regards,

Faseeh

 

[prasaddn]

Hi Williams FK,


I assume you have your data in Al cell and so I use the below formulas:


Formula 1: In Col C (C1) To fetch numbers

=RIGHT(A1,SUMPRODUCT(--((MID(A1,ROW(OFFSET(Sheet4!$K$1,0,0,LEN(A1))),1))={"0","1","2","3","4","5","6","7","8","9"})))


This is a CSE formula, i.e. after you enter the formula instead of pressing "enter key", you need to press "Cntr + Shift + Enter" keys. This will embrass { } in the cell.


Formula 2: In Col B (B1) To fetch Alpha

=LEFT(A1,LEN(A1)-LEN(C1))

Just a normal formula.


You can drag this down to what ever length you need.


Regards,

Prasad DN

 

[prasaddn]

My bad, i didn't read William telling that it is address references.


That it was a normal alphanumeric data.


Good one Faseeh!!


Regards,

Prasad DN

 

[Faseeh]

Thanks Prasaddn!


Faseeh

 

[prasaddn]

In case somebody wants use the formula mentioned above, can use it without reference to "sheet4"


=RIGHT(A1,SUMPRODUCT(--((MID(A1,ROW(OFFSET($K$1,0,0,LEN(A1))),1))={"0","1","2","3","4","5","6","7","8","9"})))

 

[SirJB7]

Hi, fkwilliams@gmail.com!


Sorry for arriving at dessert time, but I think that you might also try this:


a) suppose your data is in A1:B3 range

[pre]

A12	12
AB12	12
AB143	143

[/pre]
b) In B1 type and copy down to B2:B3:

=FILA(INDIRECTO(A1)) -----> in english: =ROW(INDIRECT(A1))


c) you'll get the results stated before (12, 12, 143)


d) needless to say that in C1:C3 you can repeat the same replacing ROW by COLUMN to obtain column number


e) for getting the column:

=IZQUIERDA(DIRECCION(B1;COLUMNA(INDIRECTO($A1));2);HALLAR("$";DIRECCION(B1;COLUMNA(INDIRECTO($A1));2))-1) -----> =LEFT(ADDRESS(B1,COLUMN(INDIRECT($A1)),2),SEARCH("$";ADDRESS(B1,COLUMN(INDIRECT($A1)),C2))-1)

Setting ADDRESS third parameter to 2 (column relative, row fixed) it introduces a $ sign that can be easily checked


Regards!