Extraction of Data from last occured number and then followed with starting text

[kiran5]

Hi All,

Need your support to extract the data with some conditions. I have data in A column like CH02A1456, MH2A215, A02B1 etc and the output in B column should come like 1456CH02, 215MH2A, 1A02B respectively. The last occurance of the number from the right side should be coming first and the rest of the character from left should be on the right side. Need an excel formula to split this as I can copy this formula where ever I wanna do this kind of activity (No macros for now please).
Uploading an excel file with few examples to get idea of my requirements. Appreciate your support on this.
Rgds,
Kiran

 

[bosco_yip]

Perhaps,

B2, copied down :

=LEFT(-LOOKUP(1,-RIGHT(A2,ROW($1:$16)))&A2,LEN(A2))

Regards
Bosco

 

[kiran5]

Amazing its working. Would really helpful if you can explain me the logic of the formula here. I know Lookup, if, right and left formulas but not able to understand the logic here as I confused with -(minus)lookup and why row($1:$16) please?

 

[bosco_yip]

Amazing its working. Would really helpful if you can explain me the logic of the formula here. I know Lookup, if, right and left formulas but not able to understand the logic here as I confused with -(minus)lookup and why row($1:$16) please?

B2 formula :

=LEFT(-LOOKUP(1,-RIGHT(A2,ROW($1:$16)))&A2,LEN(A2))

This part of formula >>
=-LOOKUP(1,-RIGHT(A2,ROW($1:$16))) is to extract the last numeric value from a text string within 16 characters

=-LOOKUP(1,-RIGHT("CH02A1456",{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}))

so that becomes

=-LOOKUP(1,{-6;-56;-456;-1456;#VALUE!;#VALUE!;……...})

=--1456

=1456

You need to understand that LOOKUP is that when the lookup value is greater than all the values in the array, then the value returned is the last numeric value in the array.

Regards
Bosco

 

[kiran5]

Wonderful explanation. Thanks a lot for this.