=left or =right
 

I have column A populated with numbers which all end in a letter for example
123A, 1258M, 999Z. In the B i woul like to the numbers excluding the letter,
ie 123, 1258, 999.

How do I do this?

=left or =right
 

=LEFT(A1;LENGTH(A1)-1)*1

Rgds. Hansueli

=left or =right
 

Slight adjustment there,

=LEFT(A1;LENGTH(A1)-1)*1
Should be
=LEFT(A1;LEN(A1)-1)*1

And just to expand a little
=LEFT(A1;LEN(A1)-1)
will return the number as text.

HTH
Martin



"Hansueli" wrote in message
...

=LEFT(A1;LENGTH(A1)-1)*1

Rgds. Hansueli

=left or =right
 

And another adjustment.

Those semi colons ;
Should be colons :



"MartinW" wrote in message
...
Slight adjustment there,

=LEFT(A1;LENGTH(A1)-1)*1
Should be
=LEFT(A1;LEN(A1)-1)*1

And just to expand a little
=LEFT(A1;LEN(A1)-1)
will return the number as text.

HTH
Martin



"Hansueli" wrote in message
...

=LEFT(A1;LENGTH(A1)-1)*1

Rgds. Hansueli

=left or =right
 

Are you sure, Martin? Colons are normally used to define an array, so a
colon in this case would be invalid syntax. The list separator, a
semi-colon in some regional settings, is more likely to be a comma in other
settings. Hence you may want =LEFT(A1,LEN(A1)-1)*1, or alternatively
=--LEFT(A1,LEN(A1)-1)
--
David Biddulph

"MartinW" wrote in message
...
And another adjustment.

Those semi colons ;
Should be colons :

"MartinW" wrote in message
...
Slight adjustment there,

=LEFT(A1;LENGTH(A1)-1)*1
Should be
=LEFT(A1;LEN(A1)-1)*1

And just to expand a little
=LEFT(A1;LEN(A1)-1)
will return the number as text.

"Hansueli" wrote in message
...

=LEFT(A1;LENGTH(A1)-1)*1

Rgds. Hansueli

=left or =right
 

Actually, those characters should match whatever the user has as a list
separator.

In the USA, most(?) people use a comma. In Europe, most(?) use a semicolon
(;)--not a colon.

I don't think many people use colons for this list separator.

MartinW wrote:

And another adjustment.

Those semi colons ;
Should be colons :

"MartinW" wrote in message
...
Slight adjustment there,

=LEFT(A1;LENGTH(A1)-1)*1
Should be
=LEFT(A1;LEN(A1)-1)*1

And just to expand a little
=LEFT(A1;LEN(A1)-1)
will return the number as text.

HTH
Martin



"Hansueli" wrote in message
...

=LEFT(A1;LENGTH(A1)-1)*1

Rgds. Hansueli



--

Dave Peterson

=left or =right
 

Yeah, I don't think I can even give myself a satisfactory explanation
for that little brain snap.

Apologies to all. I'm off to bed.

Regards
Martin


"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Are you sure, Martin? Colons are normally used to define an array, so a
colon in this case would be invalid syntax. The list separator, a
semi-colon in some regional settings, is more likely to be a comma in
other settings. Hence you may want =LEFT(A1,LEN(A1)-1)*1, or
alternatively =--LEFT(A1,LEN(A1)-1)
--
David Biddulph

"MartinW" wrote in message
...
And another adjustment.

Those semi colons ;
Should be colons :

"MartinW" wrote in message
...
Slight adjustment there,

=LEFT(A1;LENGTH(A1)-1)*1
Should be
=LEFT(A1;LEN(A1)-1)*1

And just to expand a little
=LEFT(A1;LEN(A1)-1)
will return the number as text.

"Hansueli" wrote in message
...

=LEFT(A1;LENGTH(A1)-1)*1

Rgds. Hansueli