LEFT function combined with INDIRECT function

[chrisgarcia78]

Hi, I have an issue trying to use the following LEFT function that automatically delete all characters after numbers and @. This works but when a row is inserted on top of C6 this formula automatically updates, I would like this formula to stay static even if a row is delected or added. I was trying to add the INDIRECT function to this formula but I can't get it to work. PLEASE HELP!

=LEFT(C$6,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"@"},C$6&"0123456789@"))-1)

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LET(c,INDEX(C:C,6),LEFT(c,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"@"},c&"0123456789@"))-1))

 

[chrisgarcia78]

Hi & welcome to MrExcel.
How about

=LET(c,INDEX(C:C,6),LEFT(c,MIN(FIND({0,1,2,3,4,5,6,7,8,9,"@"},c&"0123456789@"))-1))

Thank you for your help, for some reason when I use this formula it uses the whole "C" column instead of C6

 

[Fluff]

What do you mean it uses the whole column? It is only using the value in C6

 

[chrisgarcia78]

Sorry I had to change the row and cell number and made a mistake, but this works perfect. Thank you so much for your time

 

[Fluff]

You're welcome & thanks for the feedback.

 

[chrisgarcia78]

One more question, it looks like this formula works in excel 2021 but when used in 2019 I get a #name? error, is there a way to use this in excel 2019?

 

[Phuoc]

2019 not LET

=LEFT(INDIRECT("C6"),MIN(FIND({0,1,2,3,4,5,6,7,8,9,"@"},INDIRECT("C6")&"0123456789@"))-1)

 

[chrisgarcia78]

Thanks a million

 

[Fluff]

A non-volatile formula for 2019

=LEFT(INDEX(C:C,6),MIN(FIND({0,1,2,3,4,5,6,7,8,9,"@"},INDEX(C:C,6)&"0123456789@"))-1)