Formula to extract first character from right side but before the numeric data

[mehidy1437]

Hi guys,

I want the below result from the string, how can I do this with formula?

Book1
	A	B
1	STRING	REQUIRED RESULT
2	BBBA123	A
3	BB1234	B
4	CC3	C
5	DD123C	D
6	F11CCD32C	D
7	F11CCE32CM	E
8	F11CCF3CM	F
9	F11CCG345C	G
Sheet1

 

[kanadaaa]

Try the below formula in B2 and copy it into its below cells by dragging.

=IF(Len(A2)>7,MID(A2,6,1),IF(Len(A2)=7,MID(A2,4,1),MID(A2,2,1)))

 

[mehidy1437]

Try the below formula in B2 and copy it into its below cells by dragging.

=IF(Len(A2)>7,MID(A2,6,1),IF(Len(A2)=7,MID(A2,4,1),MID(A2,2,1)))

thanks for the response, but I'm looking for a dynamic formula coz string size can be changed.
it does not work for the below string.

BBddddBA123

 

[lrobbo314]

Could use VBA.

Book1
	A	B	C
1	STRING	REQUIRED RESULT	Custom Function
2	BBBA123	A	A
3	BB1234	B	B
4	CC3	C	C
5	DD123C	D	D
6	F11CCD32C	D	D
7	F11CCE32CM	E	E
8	F11CCF3CM	F	F
9	F11CCG345C	G	G
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=fc(A2)

Function FC(s As String)
Dim bt() As Byte: bt = s
Dim b As Boolean: b = False

For i = UBound(bt) - 1 To 0 Step -2
    If bt(i) > 47 And bt(i) < 58 Then
        b = True
    End If
    
    If bt(i) > 64 And bt(i) < 91 And b Then
        FC = Chr(bt(i))
        Exit Function
    End If
Next i
End Function

 

[kanadaaa]

thanks for the response, but I'm looking for a dynamic formula coz string size can be changed.
it does not work for the below string.

You didn't mention that.
We need the rule of how the result is calculated then.

 

[mehidy1437]

Could use VBA.

Book1
	A	B	C
1	STRING	REQUIRED RESULT	Custom Function
2	BBBA123	A	A
3	BB1234	B	B
4	CC3	C	C
5	DD123C	D	D
6	F11CCD32C	D	D
7	F11CCE32CM	E	E
8	F11CCF3CM	F	F
9	F11CCG345C	G	G
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=fc(A2)

Function FC(s As String)
Dim bt() As Byte: bt = s
Dim b As Boolean: b = False

For i = UBound(bt) - 1 To 0 Step -2
    If bt(i) > 47 And bt(i) < 58 Then
        b = True
    End If
   
    If bt(i) > 64 And bt(i) < 91 And b Then
        FC = Chr(bt(i))
        Exit Function
    End If
Next i
End Function

It's given me the perfect result, many thanks.

Isn't it possible to do with the excel formula, without VBA?

 

[lrobbo314]

This seems to work.

Book1
	A	B	C
1	STRING	REQUIRED RESULT	Formula
2	BBBA123	A	A
3	BB1234	B	B
4	CC3	C	C
5	DD123C	D	D
6	F11CCD32C	D	D
7	F11CCE32CM	E	E
8	F11CCF3CM	F	F
9	F11CCG345C	G	G
Sheet1

Cell Formulas
Range	Formula
C2:C9	C2	=MID(A2,MAX((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1))>=65)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1))<=90)*(CODE(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1))>=48)*(CODE(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1))<=57)*ROW(INDIRECT("1:"&LEN(A2)-1))),1)

 

[mehidy1437]

It's not giving me the correct result in excel 2016

Formula to extract first character from right side but before the numeric data.xlsm
	A	B	C
1	STRING	REQUIRED RESULT	Formula
2	BBBA123	A	#VALUE!
3	BB1234	B	#VALUE!
4	CC3	C	#VALUE!
5	DD123C	D	#VALUE!
6	F11CCD32C	D	F
7	F11CCE32CM	E	F
8	F11CCF3CM	F	F
9	F11CCG345C	G	F
Sheet3

Cell Formulas
Range	Formula
C2:C9	C2	=MID(A2,MAX((CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1))>=65)*(CODE(MID(A2,ROW(INDIRECT("1:"&LEN(A2)-1)),1))<=90)*(CODE(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1))>=48)*(CODE(MID(A2,ROW(INDIRECT("2:"&LEN(A2))),1))<=57)*ROW(INDIRECT("1:"&LEN(A2)-1))),1)

 

[lrobbo314]

I'm on 365. Might need to be entered as an array formula to work on 2016.

Click on cell C2 and hit the F2 key to edit the formula. Then hit Control+Shift+Enter and see if that makes it work.

 

[mehidy1437]

Many thanks, it's working fine with CSE, I just have added the UPPER function to avoid the lower case limitation in the formula.

I would like to have the formula without CSE in excel 2013/2016, if possible.

Formula to extract first character from right side but before the numeric data.xlsm
	A	B	C
1	STRING	REQUIRED RESULT	FormulaCSE
2	BBBAaaaaAc123	A	C
3	BB1234	B	B
4	CC3	C	C
5	DD123C	D	D
6	F11CCD32C	D	D
7	F11CCE32CM	E	E
8	F11CCF3CM	F	F
9	F11CCGffffff3444444444444444444444444eee555Cccc	G	E
Sheet3

Cell Formulas
Range	Formula
C2:C9	C2	=MID(UPPER(A2),MAX((CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(UPPER(A2))-1)),1))>=65)*(CODE(MID(UPPER(A2),ROW(INDIRECT("1:"&LEN(UPPER(A2))-1)),1))<=90)*(CODE(MID(UPPER(A2),ROW(INDIRECT("2:"&LEN(UPPER(A2)))),1))>=48)*(CODE(MID(UPPER(A2),ROW(INDIRECT("2:"&LEN(UPPER(A2)))),1))<=57)*ROW(INDIRECT("1:"&LEN(UPPER(A2))-1))),1)
Press CTRL+SHIFT+ENTER to enter array formulas.