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

ABC_1010101_LOLL_A_Group1

ABC_1010301_LOLL_A_Group2

BBC_1010121_LOLL_A_Group3

ABC_1310101_LOLL_A_Group4

ABC_1010501_LOLL_A_Group5

ABC_1013101_LOLL_A_Group6

AAC_1014101_LOLL_A_Group7

ABC_1010181_LOLL_A_Group8

ABC_2010101_LOLL_A_Group9

ABC_6010101_LOLL_A_Group10

ACC_1010101_LOLL_A_Group11

ABC_1040101_LOLL_A_Group12

<colgroup><col></colgroup><tbody>
</tbody>

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.

ABC_10101051_LOLL_A_Group1

ABC_10101_LOLL_A_Group2

BBC_1010222121_LOLL_A_Group3

ABC_131012201_LOLL_A_Group4

ABC_10888810501_LOLL_A_Group5

ABC_101301_LOLL_A_Group6

AAC_1014101_LOLL_A_Group7

ABC_1010181_LOLL_A_Group8

<colgroup><col></colgroup><tbody>
</tbody>

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