concatenate and then take off last #

[Guest]

What can I add to the following formula so that it will take off the last
digit? Because it will always have that zero on the end that I don't want.
Thanks!

As in:
A6: 020605006AA20

=CONCATENATE("", RIGHT(A6,8))

 

[Roger Govier]

Hi

Try
=CONCATENATE("", MID(A6,6,7)

Regards

Roger Govier

 

[Guest]

=mid("a6",1,len("A6")-1)


- - Mark

 

[Guest]

Thanks... the following worked (just slightly diff from yours)... THanks!
=CONCATENATE("",MID(A6,9,8))

 

[Dave Peterson]

=left(a6,len(a6)-1)

You don't need to concatenate to force it to be a string. =Left() will return a
string.

 

[Guest]

if you use this formula =CONCATENATE("",MID(A6,9,8)) then
I hope the length of the numbers is always 9

......!!!!

 

[Roger Govier]

Hi Nicki

From your original example, I assumed you wanted
5066AA2 out of the value in A6, which is what I was trying to achieve.
I also assumed that all of your data was of the same format.
Your solution, must be referring to a much longer string in A6 for it to work.
As pointed out by Excelforeman, the more generalised way would be to look at
the length of the string, and the formula should be (I think, if my
interpretation of what you want is correct), as follows
=CONCATENATE("",MID(A6,LEN(A6)-8,7))
or as Dave rightly points out, you don't need the concatenation so
=MID(A6,LEN(A6)-8,7)

Regards

Roger Govier