Using 'Right' and 'Left' formulas - maybe(?)

[joelexcel]

Needing to get these 7 digit numbers on their own, thinking it's a combination of the Left and Right formulas

Number of characters is not constant so preferably wanting to trigger on the _ (underscores).

Any help greatly appreciated


[TABLE="width: 230"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ABC_1010101_LOLL_A_Group1[/TD]
[/TR]
[TR]
[TD]ABC_1010301_LOLL_A_Group2[/TD]
[/TR]
[TR]
[TD]BBC_1010121_LOLL_A_Group3[/TD]
[/TR]
[TR]
[TD]ABC_1310101_LOLL_A_Group4[/TD]
[/TR]
[TR]
[TD]ABC_1010501_LOLL_A_Group5[/TD]
[/TR]
[TR]
[TD]ABC_1013101_LOLL_A_Group6[/TD]
[/TR]
[TR]
[TD]AAC_1014101_LOLL_A_Group7[/TD]
[/TR]
[TR]
[TD]ABC_1010181_LOLL_A_Group8[/TD]
[/TR]
[TR]
[TD]ABC_2010101_LOLL_A_Group9[/TD]
[/TR]
[TR]
[TD]ABC_6010101_LOLL_A_Group10[/TD]
[/TR]
[TR]
[TD]ACC_1010101_LOLL_A_Group11[/TD]
[/TR]
[TR]
[TD]ABC_1040101_LOLL_A_Group12[/TD]
[/TR]
</tbody>[/TABLE]


If anything further needed to address, please advise.

Kind regards
Joel

 

[Takae]

please try
=MID(A1,FIND("_",A1)+1,7)

 

[joelexcel]

please try
=MID(A1,FIND("_",A1)+1,7)

Hi Takae, thank you for that, that works

What if the what if the number of characters for the numbers differ, e.g.

[TABLE="width: 307"]
<colgroup><col></colgroup><tbody>[TR]
[TD]ABC_10101051_LOLL_A_Group1[/TD]
[/TR]
[TR]
[TD]ABC_10101_LOLL_A_Group2[/TD]
[/TR]
[TR]
[TD]BBC_1010222121_LOLL_A_Group3[/TD]
[/TR]
[TR]
[TD]ABC_131012201_LOLL_A_Group4[/TD]
[/TR]
[TR]
[TD]ABC_10888810501_LOLL_A_Group5[/TD]
[/TR]
[TR]
[TD]ABC_101301_LOLL_A_Group6[/TD]
[/TR]
[TR]
[TD]AAC_1014101_LOLL_A_Group7[/TD]
[/TR]
[TR]
[TD]ABC_1010181_LOLL_A_Group8[/TD]
[/TR]
</tbody>[/TABLE]


Is there are way to count the characters based on numbers of characters between the _'s? Another 'Find' inside a 'Len' maybe?


Cheers
Joel

 

[Takae]

Is this ok?

=LEFT(MID(A1,FIND("_",A1)+1,100),FIND("_",MID(A1,FIND("_",A1)+1,100))-1)

 

[joelexcel]

Very nice!



Thanks Takae