Extricate strings from cells based on the non-alphanumeric char

[ellison]

Hi, we are trying to extricate strings from cells and have been trying to create something like the below.

Col B: Text-Original: containing alpha (a-z or A-Z) numeric (0-9) and non alpha numeric characters. (Sometimes contains also is blank)
Col D: Extricate-01: up to the last non-alpha numeric character
Col E: Extricate-02: up to the 2nd last non-alpha numeric character
Col F: Extricate-03: up to the 3rd last non-alpha numeric character
etc

find-text-to-left-of-last-nonalphuanumeric-01.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Row	Text-Original	Text-V2: Tokenised a-z = L A-Z = L 0-9 = 9 rest of NonAlphaNumerics= _	Extricate-01: 1st last	Extricate-02: 2nd last	Extricate-03: 3rd last	Extricate-04: 4TH last	Extricate-05: 5TH last	Extricate-06: 6th last	Extricate-07: 7th last		Manual Notes (not needed, just to help explain)
2	2	_ mix - of ch@racters!	__LLL___LL_LL_LLLLLLL_	_ mix - of ch@racters	-	-	_ mix -	_ mix	_			NaN characters X 7: 22, 14, 11, 8, 7, 2, 1
3	3	12345 AND 789	99999_LLL_999	12345 AND	12345							NaN characters X 2: 10, 6, NB text contains upper and lower case
4	4											NB sometimes contains blanks [0]
5	5	Text to be changed	LLLL_LL_LL_LLLLLLL	Text to be	Text to	Text						NaN characters X 3: 11, 8, 5
6	6	Texttobe-changed	LLLLLLLL_LLLLLLL	Texttobe								NaN characters X 1: 9
7	7	Extricate this ;	LLLLLLLLL_LLLL__	Extricate this	Extricate this	Extricate						NaN characters X 3: 16, 15, 10
8	8	nononalphanumeric	LLLLLLLLLLLLLLLLL									NaN characters: 0
Sheet1

Cell Formulas
Range	Formula
C2:C8	C2	=convertentry(B2)

NB we got some (amazing!) code from a mrexcel contributor that actually converts strings into:
a-z: L
A-L: L
0-9: 9
non alpha numeric: _

I've put the code below just in case it comes in handy...





Code on the "convertentry":

Function ConvertEntry(ByVal myEntry As String) As String
  Dim X As Long
  For X = 1 To Len(myEntry)
    If Mid(myEntry, X) Like "[A-Za-z]*" Then
      Mid(myEntry, X) = "L"
    ElseIf Mid(myEntry, X) Like "#*" Then
      Mid(myEntry, X) = "9"
    Else
      Mid(myEntry, X) = "_"
    End If
  Next
  ConvertEntry = myEntry
End Function

Huge thanks for taking a look

 

[Anonymous1378]

Do you consider @ to be alphanumeric?

If you do, change _d to

(_c>=97)*(_c<=122)+(_c>=65)*(_c<=90)+(_c>=48)*(_c<=57)+(_c=64)

Book1
	A	B	C	D	E	F	G	H	I
1	Row	Text-Original	Extricate-01: 1st last	Extricate-02: 2nd last	Extricate-03: 3rd last	Extricate-04: 4TH last	Extricate-05: 5TH last	Extricate-06: 6th last	Extricate-07: 7th last
2	2	_ mix - of ch@racters!	_ mix - of ch@racters	_ mix - of ch	_ mix - of	_ mix -	_ mix	_ mix	_
3	3	12345 AND 789	12345 AND	12345
4	4
5	5	Text to be changed	Text to be	Text to	Text
6	6	Texttobe-changed	Texttobe
7	7	Extricate this ;	Extricate this	Extricate this	Extricate
8	8	nononalphanumeric
Sheet1

Cell Formulas
Range	Formula
C2:J2,C7:E7,C5:E5,C4,C6,C8,C3:D3	C2	=LET( _a,B2, _b,SEQUENCE(LEN(_a)), _c,CODE(MID(_a,_b,1)), _d,(_c>=97)*(_c<=122)+(_c>=65)*(_c<=90)+(_c>=48)*(_c<=57), _e,SORT(FILTER(_b,NOT(_d),""),,-1), IFERROR(TRANSPOSE(REPLACE(_a,_e,LEN(_a),"")),""))
Dynamic array formulas.

 

[ellison]

That really is absolutely wonderful - HUGE thanks!