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

 

[Rick Rothstein]

Here is another array-entered** formula (it is non-Volatile) that you can try...

=MID(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))-LEN(RIGHT(LEFT(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))),COUNT(,-RIGHT(LEFT(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))),ROW($1:$15)))-1)),1)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[mehidy1437]

Here is another array-entered** formula (it is non-Volatile) that you can try...

=MID(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))-LEN(RIGHT(LEFT(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))),COUNT(,-RIGHT(LEFT(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))),ROW($1:$15)))-1)),1)

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Thanks, Rick.
Is it possible to do without CSE?

 

[Rick Rothstein]

With the exception of using a VBA macro or UDF, no, not that I am aware of.

 

[mehidy1437]

Thank you.

 

[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

Hi dear, how can grab two characters with this function?

 

[Rick Rothstein]

Hi dear, how can grab two characters with this function?

I know you posted this question against a UDF (user defined function) posting, but you can use a slight modification to the array-entered** formula I posted earlier to get the 2 characters before the last number...

=MID(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))-LEN(RIGHT(LEFT(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))),COUNT(,-RIGHT(LEFT(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))),ROW($1:$15))))),2)

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
.

 

[Rick Rothstein]

Hi dear, how can grab two characters with this function?

Besides the formula solution I just gave you immediately above in Message #16, here is a UDF (user defined function) that will retrieve as many characters before the last number as you specify...

Function GetCharsBeforeLastNum(ByVal S As String, HowMany As Long) As String
  Dim X As Long, Arr() As String
  S = S & "Z"
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "#" Then Mid(S, X) = " "
  Next
  Arr = Split(Application.Trim(S))
  GetCharsBeforeLastNum = Right(Arr(UBound(Arr) - 1), HowMany)
End Function

To use the function, simply pass in a text string or cell reference to a text string as the first argument and the number of characters to retrieve as the second argument. For example...

=GetCharsBeforeLastNum(A1,2)

 

[mehidy1437]

Besides the formula solution I just gave you immediately above in Message #16, here is a UDF (user defined function) that will retrieve as many characters before the last number as you specify...

Function GetCharsBeforeLastNum(ByVal S As String, HowMany As Long) As String
  Dim X As Long, Arr() As String
  S = S & "Z"
  For X = 1 To Len(S)
    If Mid(S, X, 1) Like "#" Then Mid(S, X) = " "
  Next
  Arr = Split(Application.Trim(S))
  GetCharsBeforeLastNum = Right(Arr(UBound(Arr) - 1), HowMany)
End Function

To use the function, simply pass in a text string or cell reference to a text string as the first argument and the number of characters to retrieve as the second argument. For example...

=GetCharsBeforeLastNum(A1,2)

Thanks a lot dear.

 

[mehidy1437]

I know you posted this question against a UDF (user defined function) posting, but you can use a slight modification to the array-entered** formula I posted earlier to get the 2 characters before the last number...

=MID(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))-LEN(RIGHT(LEFT(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))),COUNT(,-RIGHT(LEFT(A2,MAX(IF(ISNUMBER(-MID(A2,ROW($1:$1000),1)),ROW($1:$1000)))),ROW($1:$15))))),2)

** Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself
.

Please help me to understand, what is ROW($1:$1000) & ROW($1:$15) doing here?