vba Remove last 5 characters from string in table column

[brentcook]

Have a table in a sheet that is connected with info on sharepoint. Wondering if there is a way to remove the last 5 characters (.xslx, .docx, .xlsm etc.) from an entire column. Any ideas?

 

[DanteAmor]

Hi Dante … Just trying to understand your Evaluate line, what is the purpose of IF({1} & what does it mean ? I have tried to produce the same line without it as shown below & it works just fine with my small sample

This only works for the first cell
    .Value = Evaluate("=mid(" & .Address & ",1,len(" & .Address & ")-5)")

To work with all cells it must be an array formula, for that the If evaluation is used, but we need it to always be true, so if ({1}, this always makes it true.


Try an example with the following in column "A":
<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:134.97px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >word1.docx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >letras1.xlsx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >word1.docx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >pres new1.pptx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >letras2.xlsx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >word2.docx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >pres new2.pptx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >letras tres.xlsx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >word tres.docx</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >pres new tres.pptx</td></tr></table>

 

[mse330]

Thanks Dante for the feedback. I tried my code without the if({1} in the sample data provided above & it works for all rows not only the first row ... Anyway, i don't want to hijack this thread for something else. I will try to read more about it

 

[DanteAmor]

Thanks Dante for the feedback. I tried my code without the if({1} in the sample data provided above & it works for all rows not only the first row ... Anyway, i don't want to hijack this thread for something else. I will try to read more about it

It's weird, did you try my data?
Can you put your test data?

 

[Fluff]

Dante
It works for me without the If function.
I suspect that the Mid function effectively "returns the row number", in the same way the If function does.

 

[DanteAmor]

Dante
It works for me without the If function.
I suspect that the Mid function effectively "returns the row number", in the same way the If function does.

Hi Fluff,
It doesn't work for me, maybe it's the excel version, I have 2007 (I know it's an old version).

 

[Fluff]

In that case it does sound like something's changed as it works with 365 and unfortunately I can't get onto my old computer with xl2013 to check with that version.

@mse330
What version of Xl are you running?

 

[mse330]

My excel version is 365 as well

 

[DanteAmor]

So it's the version, from what I've seen, 365 already has another handle when it comes to array formulas.

 

[Fluff]

Whilst I don't have the new dynamic array formulae as yet, it might have something to do with it.

 

[DanteAmor]